If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:
SELECT * INTO LIT FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Sheet1$]')
- Remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$) and that plain object names represent Excel named ranges.
- In some circumstances, especially when you designate the Excel source data by using the table name instead of a SELECT query, the columns in the destination SQL Server table are rearranged in alphabetical order.