How to Import Data into SQL Server From Access / Excel

Import data from Access / Excel into SQL Server using TSQL:

 

Use the following SQL statements to import the data

 

--FROM EXCEL

EXEC sp_addlinkedserver @server = N'XLSERVER', @srvproduct=N'ExcelData', @provider=N'Microsoft.Jet.OLEDB.4.0',

      @datasrc=N'C:\Manipulation\DCNetworkVendorData.xls', @provstr=N'EXCEL 8.0' ;

 

SELECT * FROM XLSERVER...[TableName]

 

 

 

-- FROM XLSX

--Don't be supprised if this doesn't work for Excel/Access using the latest 'Microsoft.ACE.OLEDB.12.0' driver (which should be called version 14 for Office 2010).

EXEC sp_addlinkedserver @server = N'XLSERVER', @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

      @datasrc=N'C:\Manipulation\DCNetworkVendorData.xlsx', @provstr=N'EXCEL 12.0' ;

 

SELECT * FROM XLSERVER...[TableName]

 

 

 

-- FROM MS ACCESS

EXEC sp_addlinkedserver  @server = N'AccessServer', @provider = N'Microsoft.Jet.OLEDB.4.0',

      @srvproduct = N'OLE DB Provider for Jet', @datasrc = N'C:\Manipulation\AccessDB.mdb';

 

SELECT * FROM AccessServer...[TableName]