Batch Inserting from Excel into SQL Server
Is there a way to do a batch update on SQL Server from a row of data in Excel? We have excel documents that contain 2000+ plus rows and need to be imported in SQL Server. Is there a way to do a batch insert of these guys without calling the database over and over to insert one row at a time?
Answer
SQL Server Integrations Services offers a wizard based import which can help you easily set up a package to import an excel file. You can even save the package and schedule it to repeat the import in the future.
You have other options, as well. If you save the excel file to a comma or tab delimited text file, you can use the BULK INSERT t-sql command. See an example on the sqlteam.com forums.
Another T-SQL option is SELECT INTO. Excel is a valid OLEDB or ODBC data source from T-SQL. Here's an example.
There's also a command line import tool included with Microsoft SQL Server called BCP. Good documentation on BCP and the other options can be found on MSDN at: http://msdn.microsoft.com/en-us/library/ms187042.aspx