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

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.