We help IT Professionals succeed at work.

Import Access data into Sql Server 2008

Medium Priority
536 Views
Last Modified: 2012-08-21
We have an Access database which we are planning to migrate to Sql Server 2008 R2. One of our developers is performing this task and today evening he came up to me and asked me the following question. I need help in answering this as I am new to Sql Server.

"
The data to be uploaded is in the Access table “TO BE UPLOADED INTO SQL”. I need to load this data in a Sql Server table.
 
The problem is that not all of the data is going into the Sql Server DB, and there are no error messages as to what records are not going in, and why. They just don’t go in to SQL Server.
The good news is that all but one August record did import OK.
 
Your thoughts?
"

He is asking me what steps needs to be taken to troubleshoot this. Can you guys help me in troubleshooting this?

Thank You.
Comment
Watch Question

How are you migrating the data?

What tools/queries/connections are you using?

Is the table strucure exactly the same?
Do you have any constraints or triggers on SqlServer that could avoid the insert?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
The Upsize Wizard of Access is good but not very informative.

In your case a better choice is most likely the SQL Server Migration Assistant:

http://blogs.technet.com/b/dataplatforminsider/archive/2012/01/30/microsoft-sql-server-migration-assistant-5-2-is-now-available.aspx
 
It takes a little more to operate it as it effectively "syncs" the table structure of your Access database with a (new) database on SQL Server. Thus, you can resync if you make design changes and - quite important - it delivers a comprehensive error report when a sync has been done.

/gustav

Author

Commented:
@cactus_data,

Thank you for the suggestion. I downloaded the SSMA for Access and installed it, created a new project and started to migrate the Access database table "TABLE_A" into Sql Server.

When i migrate i want to migrate "TABLE_A" from access into "TABLE_B" in Sql server. But when I do "convert, load and migrate", it creates a new table "TABLE_A" in Sql sever and the migration completes. I do not want this. I want to load data into a pre-existing "TABLE_B" in Sql Server.

How can I do this? Please help !!!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I don't understand why you would do this, but you can pick "convert, load and migrate" individually.

/gustav

Author

Commented:
@cactus_data,

Can you be more specific?

I can do "convert Schema" individually but how do I map "Table_A" to "Table_B" and in which step do I do that?

When I try to do "Migrate Data", it errors out saying "Table_A" does not exist in Sql Database. I do not want to create this table, instead load into existing table.

I really appreciate your help..
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If you update the schema from SQL Server, it should find your Table_A.

Or rename Table_A, create run the update, rename the new Table_A to Table_B, rename the first table back to Table_A.

Or just backup Table_A and restore it after the conversion.

/gustav

Explore More ContentExplore courses, solutions, and other research materials related to this topic.