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')