Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Import Data to SQL Server from Excel or Access using TSQL Script

Rate me:
Please Sign up or sign in to vote.
3.00/5 (6 votes)
17 Nov 20073 min read 174.7K   34   8
An article on importing data from external data sources to SQL server without using wizards

Introduction

After using The Code Project for many years to help out with solutions, I felt it was only fair to start making contributions myself. This is my first article, so please be kind.

Background

Ever needed to import data to SQL Server from Access which needs more tweaking than the SSIS wizard will allow? Ever wondered if it can be done using T-SQL scripts?

As a professional developer, I regularly have to import data from legacy systems (Excel and Access) to SQL Server before 'go-live' for clients. Normally, this can be done using the SQL Server SSIS wizards. On the most recent occasion, I found myself needing to import data from a one-table Access system to 38 relational SQL tables. The source data was a mess and needed a lot of tidying and transformation before being imported.

With over a million rows of data (mostly stored as Access Text fields), I needed a solution which would automate the process in the easiest way possible. With this in mind, I looked to SQL scripts, whereby I could manipulate the data (i.e. replace country names with lookup codes after first inserting the country names into a countries table as a simple example), thus creating a fully relational model with the source data provided.

Using the Code

The example shown here can be used from SQL Server Management Studio, or from an O-SQL command.

Before using the example, you must allow AdHoc remote queries in SQL Server 2005. To do this, start the Surface Area Configuration tool from the SQL Server 2005 installation directory, and select Surface Area Configuration for Features.

Select the server you wish to execute the import on, and select the entry in the component list titled "Ad Hoc Remote Queries". On the right of the page, ensure that the checkbox for "Enable OPENROWSET and OPENDATASOURCE" is checked. Apply your changes and close the tool.

Now the fun part!

I used a temporary table to import the data as it was for a one-off import, but there's nothing to stop you using a permanent table for the task.

The first task then, is to create your table (this example uses a temporary table hence the # before the table name. To create a permanent table, simply specify the Table name without the leading #):

SQL
CREATE TABLE [dbo].[#tblImport](
    [Field1] [int] not null,
    [Field2] [varchar] (255) null,
    [Field3] [varchar] (255) null,
    [Field4] [datetime] null)

Now we need to get the data from the external source.

The source I used was an Access database, which had "illegal" field names, i.e. spaces, ampersands etc. My only way to get the data into SQL Server was to specify both the source and destination fields explicitly.

To open the Access datasource, you use the SQL Command OPENDATASOURCE as follows:

SQL
OPENDATASOURCE(provider_name,init_string)

So, assuming that the Access data source is named My Database.mdb and is located in the root of the C: drive, the OPENDATASOURCE statement would look like this:

SQL
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','C:\My Database.mdb')

Now we need to import the data. In my case, I only had one table to import but had to transform it to multiple SQL tables. Once you have the source data, you can use standard T-SQL statements to do whatever you need to do.

To import the data into the temporary table created above, use the following:

SQL
INSERT INTO [dbo].[#tblImport]
    Field1,Field2,Field3,Field4
SELECT [Field1],[Field2],[Field3],[Field4] FROM OPENDATASOURCE_
    ('Microsoft.Jet.OLEDB.4.0','C:\My Database.mdb')...[TableName]

Note the ...[TableName] at the end of the statement. The THREE dots are essential, and the TableName is the name of the source table you are importing from.

Once the import stage above is complete, transform the data as necessary to import to your SQL tables.

If, like me, you've used a temporary table to do the initial import, don't forget to drop it once you've finished!!

SQL
DROP TABLE [dbo].[#tblImport]

Conclusion

Importing data using T-SQL is a very powerful way of controlling the final result, and the possibilities of what you can do with the imported data re: transformation are endless if you have a good command of SQL.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United Kingdom United Kingdom
A professional VB.NET, ASP.NET and SQL developer with over 10 years commercial experience.

I started out as an e-commerce programmer using ASP 3 and mysql, but now specialise in MS.NET technologies for Aldex Software in the UK.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Joseph Masters18-Dec-12 3:33
Joseph Masters18-Dec-12 3:33 
QuestionT-SQL or SSIS and importing from Excel Pin
Member 384354318-Jul-11 6:51
Member 384354318-Jul-11 6:51 
Generalexception running the code Pin
daizyshen11-Oct-10 17:20
daizyshen11-Oct-10 17:20 
QuestionCould not find installable ISAM Pin
Masterhame27-Feb-08 20:21
Masterhame27-Feb-08 20:21 
AnswerRe: Could not find installable ISAM Pin
Ashutosh Phoujdar3-Jun-08 0:34
Ashutosh Phoujdar3-Jun-08 0:34 
GeneralGood article....question though Pin
Rupert Todd19-Dec-07 9:03
Rupert Todd19-Dec-07 9:03 
QuestionHow do I use this approach to read from excel file? Pin
extremeg18-Nov-07 8:39
extremeg18-Nov-07 8:39 
Nice one!

Please what is the valid value for the provider name parameter when fetching data from an excel file. Also, does the [TableName] in the query correspond to the Worksheet name in excel. Please clarify. I'm considering using this approach to fetch data from an excel file.

Thanks.

...the mind is not a vessel to be filled but a fire to ignited

AnswerRe: How do I use this approach to read from excel file? Pin
Waynes-world18-Nov-07 8:58
Waynes-world18-Nov-07 8:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.