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

Advertisements
  1. Hi A Rahim,

    I get MSG 7403, ‘The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered.’

    How do I register the Microsoft.Jet.OLEDB.4.0?

    Thanks,
    Harmony

  2. I have read some excellent stuff here. Certainly worth
    bookmarking for revisiting. I surprise how a lot attempt you
    set to make this kind of great informative web site.

  1. January 3rd, 2011

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: