Click here to Skip to main content
969 members (157 online)
Click here to Skip to main content
Add your own
alternative version

Stats

2.4K views
Posted 12 Apr 2012

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

, 12 Apr 2012
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.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
RA-Web1 | 2.8.170624.1 | Last Updated 12 Apr 2012
Article Copyright 2012 by Waynes World
Everything else Copyright © RootAdmin, 1999-2017
Layout: fixed | fluid