Import data from Access to existing SQL Table

[Kev86]

I have been looking at moving my Access database over to an SQL server. I have started to do this and been amending some of the tables in SQL to make sure they are fit for purpose. I have also created a test version of my SQL database which has all the relevant tables and i have truncated the data so the tables are all set but there is no data inside.



I now want to be able to import the data from my current live Access database tables into the existing blank SQL tables. I have tried to export from Access by selecting a table and clicking Export then clicking ODBC Database then selecting my test SQL database from the Machine Data Source tab however when I then click ok I am met with an ODBC-call failed error which states the object name already exists.

Obviously it's trying to move the data and also create a new table, however i just want to move the data within the table to an existing table within SQL rather than trying to move the table across.

Any ideas how I do this?

[madpiet]

What if you (temporarily) create a bunch of linked tables in your Access DB that basically point to the SQL Server tables and then you can just create a bunch of append queries and then delete the linked tables. The one catch part may be that you need to use

SET IDENTITY_INSERT ON

for all of the tables that you're appending to.

Before doing anything, read this article: SET IDENTITY_INSERT (Transact-SQL) - SQL Server | Microsoft Learn

Oh, and get all the scripts working on a dummy database. Then when all your tests work, do it for the real database. Looks like the basic pattern is going to be:

-- turn on to allow insertion into identity columns
SET IDENTITY_INSERT schema.tableName ON

/* do inserts here */

-- turn off to set back to normal
SET IDENTITY_INSERT schema.tableName OFF

[Minty]

You can use SSMA (Microsoft SQL Server Migration Assistant for Access) for this and it's significantly faster than any other method I have come across, particularly with large tables.
It will tell you it's deleting the target data then insert the latest data.

If you save the task as a project you can easily reconnect and re-run it multiple times during set up and testing of a big migration.

[Kev86]

Hi Minty, I don't think we have that software in the company I work for, any other ideas of how to move it across? If not then i might have to ask whether i can get SSMA

[Kev86]

I'm not much of an expert on SQL so might struggle with this suggestion. I will take a look at the link you provided.

[madpiet]

SSMA is a free download. (SQL Server Migration Assistant) It's here: Download Microsoft SQL Server Migration Assistant for Access from Official Microsoft Download Center

[ArviLaanemets]

Is the Access 32-bit or 64-bit version? We had a problem with 32-bit Access after we switched to 64-bit SQL Server because such connection wasn't supported. The solution we used was to install 32-bit SQL Server Express, read data from Access DB there, and the read data into 64-bit SQL from 32-bit one.

[Kev86]

Madpiet I found it last night and tried to download and installed but was met with a requirement of admin login so i will need to discuss with IT to get this resolved.

[Kev86]

Hi Arvi, It's a 64-bit version. I have looked on our company portal and i am able to download Microsoft SQL Server Express, it doesn't tell me what bit version it's used for though. Is that easier to use with importing data from Access to an SQL database? What is the different between SQL Server Express and SQL Server Management Studio?

[NoellaG]

IMO the best method is to start from SQL server and import the access data. You can create SSIS packages for that or use the SQL import wizard. If you want to reuse the packages start with a truncate table statement to empty existing data in the SQL tables.
SQL server Express is a free version of SQL Server with a few limitations such as a max database size of 10 GB. SSIS is included with SQL express. SQL Server Management Studio is a graphical interface tool you can use with every version of SQL server, included SQL express.

[Kev86]

Hi Noella, I have truncated the tables already however when I go to my database and right click it and go to Tasks then Import data. I then select the data source as Microsoft Access (Microsoft Jet Database Engine) but when i browse and find the Access database with the data i want to import it doesn't allow me to. I think it's looking for a .mdb file whereas my database is .accdb so when i try to select my database it says it's an unrecognized format.

[ArviLaanemets]

What is the different between SQL Server Express and SQL Server Management Studio?

You don't have pay! But database size is more limited, and you can't create Jobs. The only reason we used it was that it was freeware. No way the company management would allow to buy licenses for 32-bit SQL Server (to get data read from single 32-bit Access DB) - additionally to 64-bit SQL Server we had.

[Minty]

SSMA will accommodate any version and "Bitness" of Access and all flavours of SQL server, and is free.
It will automatically truncate and insert new/replacement primary key fields, and is by far the easiest route without needing much knowledge of either SQL Server or Access.

Additionally, it will create tables and you can set your own data-type mappings, so you don't get any surprises.
It's designed to do exactly what you need. If you can get it installed on your machine it will be the simplest method.

[Kev86]

Thank you Minty, I will ask IT about getting SSMA.