OPENROWSET: Import data into SQL Server tables

The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet, directly from your SQL script. Coupled with the “SELECT * INTO” command, the OPENROWSET feature can import data from an Excel sheet into a table in SQL Server or MSDE.

Run the following command. This example uses the OLE DB Provider for Microsoft Excel to access sheet1 in the Excel file c:book1.xls.

 

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET(‘MSDASQL’,
‘Driver={Microsoft Excel Driver (*.xls)};DBQ=c:book1.xls‘,
‘SELECT * FROM [sheet1$]’)

 

table1 will be created in the db1 database. The content of this table will be imported from the sheet1 worksheet in your c:book1.xls Excel file.