asked on

Bulk Import Access Table to SQL Server

Hi,

What's the best to bulk import an Access table into SQL Server 2012?

I tried using this

SELECT * INTO [E2].[dbo].[Test]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\TEST.ACCDB';;, 'SELECT * FROM [Test]')

and it runs extremely slow.

Thanks.

Surendra Nath

Are you doing this as a one-off exercise or are you trying to do this as part of an application?

Assuming Access 2003, If you are doing it as a one-off then try this:

Open the table

File (Menu) --> Export

Save as Type 'Text Files'

Don't use the 'save formated' option

That will bring up the Export Text Wizard

Choose Delimited, click next

Choose 'Comma' delimiter and " as Text qualifier.

Choose 'Include Field names on First Row' if you want column headings

Click next

Give it a name and click finish

That will give you a CSV file.

If you want to do it as part of the application take a look at docmd.TransferText in MS Access help.

once you do the above steps and get the CSV file out,
Then you can either bulk insert or BCP commands to load them into your table pretty fast.

akivashapiro

ASKER

I'm doing this as part of an application, but I'd much rather use a Transact-SQL statement to import the data directly from the Access table into a SQL Table. Please advise. Thanks.

ASKER CERTIFIED SOLUTION

akivashapiro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

akivashapiro

ASKER

It was the only option I could find. I worked very quickly - over a million rows export and import in less than a minute. But, you have to deal with data anomalies, such as hidden characters, prior to exporting to text.