Avance Zone

SET @server = ‘XLTEST_SP’
SET @srvproduct = ‘Excel’
SET @provider = ‘Microsoft.Jet.OLEDB.4.0′
SET @datasrc = ‘D:\my_excel_file.xls’
SET @provstr = ‘Excel 8.0′
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog
GO
SELECT * FROM XLTEST_SP…Sheet1$
GO

EXCEL SHEET TO SQL SERVER DATABASE

 

SELECT *

FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,

                ‘Excel 8.0;Database=E:\example_arb.xls’,

                ‘SELECT * FROM [Sheet1$]‘)

 

 

OPENROWSET ( ‘provider_name‘,

              { ‘datasource‘; ‘user_id‘; ‘password‘ | ‘provider_string‘ },

              { [ catalog.][schema.]object | ‘query‘ }

           )

OR SIMPLY

OPENROWSET ( ‘provider_name‘, ‘provider_string‘, ‘query‘ )

 

 

 

SELECT *

 

FROM OPENDATASOURCE

 

(‘Microsoft.Jet.OLEDB.4.0′

 

,‘Data Source=”E:\example_arb.xls”;Extended Properties=Excel 8.0′)…[Sheet1$]

 

 

 

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.