How to Export Data to or Import Data from Excel in SQL Server 2005

It’s possible to export data to Excel and import data from Excel using T-SQL Command in SQL Server 2005.

First of all, you need to enable ‘Ad Hoc Distributed Queries’ in SQL Server 2005. Prior to this you may have to enable ‘show advanced options’, if it requires.

Create an Excel file and create columns like the Table’s columns that you are going to export data from.

-- Enable 'show advanced options'
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable 'Ad Hoc Distributed Queries'
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

-- Export data from SQL Server to Excel
INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Test.xls',
'SELECT * FROM [Sheet1$]')
SELECT * FROM Table_SaleSummary 

-- Import data from Excel to SQL Server
INSERT INTO Table_SaleSummary
SELECT * FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Test.xls',
'SELECT * FROM [Sheet1$] WHERE MonthID IS NOT EQUAL TO NULL') 

Thanks
A Rahim Khan

2010

04/18

CATEGORY

SQL Server 2005

TAGS

export to excel
import from excel
SQL Server
SQL Server 2005

Write comment