• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Import Access data into Sql Server 2008

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.
0
farooqhussain
Asked:
farooqhussain
  • 3
  • 2
1 Solution
 
joaoalmeidaCommented:
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?
0
 
Gustav BrockCIOCommented:
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
0
 
farooqhussainAuthor 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 !!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

/gustav
0
 
farooqhussainAuthor 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..
0
 
Gustav BrockCIOCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now