Posted By

hairajeshk on 08/04/10

Tagged

Versions (?)

Last Edited at 08/04/10 04:35pm

Statistics

Viewed 960 times

Favorited by 0 user(s)

Related snippets

Import data into SQL Server using the ad hoc distributed queries from Excel

/ Published in: SQL

Import data into SQL Server using the ad hoc distributed queries from Excel

Highlighted TextExpand

There are many ways to import data from Excel into SQL Server. Some of them are as below:
â€¢         Using SQL Server Integration Services. (This technique allows very detailed control over the data being imported but is complex & time-consuming to set up.) 
â€¢         SQL Server linked servers (This technique requires that you create a permanent linked server pointing to the Excel source file.) 
â€¢         SQL Server ad-hoc distributed queries (This is the quickest way to import data. It creates a temporary linked server to the Excel sheet on the fly & pulls the data.)

how to import data into SQL Server using the ad hoc distributed queries.
First of all, you need to make sure that "Allow Ad Hoc Distributed Queries" option in SQL Server Surface Area Configuration window is set enabled. This can be done as below:

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO 
  
You can now use the below query to import data from Excel 2007/2003 (.xlsx/xls) sheet into SQL Server.
 
create table TestTable (id int, name varchar(100))
GO
INSERT INTO TestTable
SELECT *  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test\Data.xlsx;Extended Properties=Excel 8.0')...[Sheet1$]

Here, we create a SQL Server table called TestTable and then pull the data from Sheet1 in the source file C:\test\Data.xlsx into the table. Note that we need to append the Excel sheetname with a $ symbol when importing the data into SQL Server. Also, make sure the data types are mapped correctly. (i.e. the query doesnâ€™t try to insert a character value into an integer column in SQL Server and so on..)
 
In case you donâ€™t have the 'Microsoft.ACE.OLEDB.12.0' provider installed on the system running SQL Server, the above query will error out. In that case, you can use the Microsoft.Jet.OLEDB.4.0 (if  installed) to import data only from Excel 2003 sheet. Also note that there is no Microsoft.Jet.OLEDB.4.0 provider for 64-bit systems. So, the below query will error out on 64-bit systems. The query is:
 
create table TestTable (id int, name varchar(100))
GO
INSERT INTO TestTable
SELECT *  FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\Data.xlsx;Extended Properties=Excel 5.0')...[Sheet1$]

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.

Posted By

Tagged

Versions (?)

Statistics

Related snippets

Import data into SQL Server using the ad hoc distributed queries from Excel

Comments

Choose a language for easy browsing: