Sometimes we need export data from excel data source to SQL. In this article we are demonstrating the export data from excel data source to SQL server table by using T-SQL Query.

I have created excel file in c drive in test folder with the name Employee.xls.


We need to import above Excel data into SQL using T-SQL Query.

This can be achieved by two ways
  1. OPENDATASOURCE
  2. OPENROWSET

1. OPENDATASOURCE

SELECT * INTO #ExcelImport FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\Employee.xls;Extended Properties=Excel 8.0')...[Emp$]

SELECT * FROM #ExcelImport

DROP TABLE #ExcelImport

Here above query Data Source is the Source path of where Excel file is in kept .[Emp$] is the Excel Sheet name.

2. OPENROWSET

SELECT * INTO #ExcelImport FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\Employee.xls', [Emp$])

SELECT * FROM #ExcelImport

DROP TABLE #ExcelImport

Here above query Database is the Source path of where Excel file is in kept .[Emp$] is the Excel Sheet name .

SELECT * INTO #ExcelImport FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\Employee.xls', 'SELECT [Name],[Salary] FROM [Emp$]')

SELECT * FROM #ExcelImport

DROP TABLE #ExcelImport

Here above query Database is the Source path of where Excel file is in kept. If you observe the 'SELECT [Name],[Salary] FROM [Emp$]’ Selecting the colums from Emp sheet.