Click here to Skip to main content
Click here to Skip to main content

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

, 17 Nov 2007
Rate this:
Please Sign up or sign in to vote.
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 #):

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:

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:

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:

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!!

 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

Share

About the Author

Waynes World
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 PinmemberJoseph Masters18-Dec-12 3:33 
QuestionT-SQL or SSIS and importing from Excel PinmemberMember 384354318-Jul-11 6:51 
Generalexception running the code Pinmemberdaizyshen11-Oct-10 17:20 
QuestionCould not find installable ISAM PinmemberMasterhame27-Feb-08 20:21 
AnswerRe: Could not find installable ISAM Pinmemberdnpro3-Jun-08 0:34 
GeneralGood article....question though PinmemberMember 471063419-Dec-07 9:03 
QuestionHow do I use this approach to read from excel file? Pinmemberextremeg18-Nov-07 8:39 
AnswerRe: How do I use this approach to read from excel file? PinmemberWaynes-world18-Nov-07 8:58 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 17 Nov 2007
Article Copyright 2007 by Waynes World
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid