1. Enable 'ad hoc distributed queries 'in SQL Server 2008 r2'
Exec sp_configure 'show advanced options', 1 go reconfigure go exec sp_configure 'ad hoc distributed queries ', 1 go reconfigure go
2. Use the OpenDataSource or OpenRowSet function to import data for a specific purpose. The following code example can also import the Excel MERs worksheet data to a new SQL Server table:
In SQL Server 2008 R2, replace Microsoft. Jet. oledb.4.0 with Microsoft. Ace. oledb.12.0.
The EXCEL object name with the dollar sign ($) appended represents the worksheet (for example, sheet1 $), and the normal object name without the dollar sign represents
The range specified by Excel.
Select * into xlimport3 from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = c: \ test \ xltest.xls; extended properties = Excel 8.0 ')... [MERs $] Select * into xlimport4 from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; database = c: \ test \ xltest.xls', [MERs $]) Select * into xlimport5 from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; database = c: \ test \ xltest.xls', 'select * from [MERs $] ')