• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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