CODEWITHSUNDEEP
sql-serverexcelbatch-filessisbatch-insert
Mike Flynn
Mike Flynn

Reputation: 24325

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?

Upvotes: 3

Views: 24432

Answers (3)

Mithrandir
Mithrandir

Reputation: 25337

Yes! Use the import/export wizard of the SSMS! Use an Excel-source and a SQL Server destination. You can also create a SSIS-Package in the BIDS or use the BULK INSERT-statement from T-SQL, if you convert your Excel-sheets in to CSV-files.

Upvotes: 1

Aaron
Aaron

Reputation: 57748

You can create an SSIS package to read your Excel file. When you create your task, you can select a connection type of "Excel", and then it helps you create an "Excel Connection Manager". Then you can easily send the data to your SQL Server table. Here's a tutorial on how to import an Excel file into SQL Server (2005). Give it a look.

Upvotes: 1

Tom Resing
Tom Resing

Reputation: 2117

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

Upvotes: 7

Related Questions