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

Importing from Access 2003 .mdb file to SQL Server 2008 and maintaining identity insert

I have a large 50 MB Access database that we have decided to upscale to MSSQL 2008. The problem is , when we do the import using MSSQL wizard, the autoid fields do not maintain their identity status.

How can I import this database and not have to delete and recreate the autonumber index?
0
souldj
Asked:
souldj
  • 4
  • 4
  • 2
3 Solutions
 
knightEknightCommented:
In SQL Server you can set the IDENTITY_INSERT property to ON before doing the insert -- just don't forget to turn it back off again when you are done!

See this link for details:  http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx
0
 
cb1393Commented:
Not sure what you mean by deleting and recreating an autonumber index. If I understand correctly, the data gets imported, but the ID column does auto-generate numbers SQL Server... correct?

If so, you should just need to do the following:

1) Make sure the ID column in SQL server is of datatype "int"

2) In the table design view, select the ID column. In the column properties, expand "Identity Specification" and select "Yes" for "(Is Identity)".

3) Save the table
0
 
souldjAuthor Commented:
How do we do this during the run time of the importing wizard?
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.

 
cb1393Commented:
Correction: "...but the ID column does auto-generate..." should read "...but the ID column does NOT auto-generate..."
0
 
souldjAuthor Commented:
cb1393,

When i try to do this I get an error message stating

"Saving changes is not permitted. The changes you have made require the following
tables to be dropped and re-created. You have either made changes to a table that
can't be re-created or enabled the option Prevent saving changes that require the
table to be re-created."

I have to manually delete the column "autoid" and then save the tabel, then modify and add a new autoid column which is set as identity insert but the problem is , a whole new batch of numbers is now created and if I have relationships with other tables based on the old values, I lose these relationships.
0
 
souldjAuthor Commented:
Ideally, I want to import the database as is .. then enable Autoid as identity column in the database.
0
 
knightEknightCommented:
See the link I provided above.  First, set the table to accept Identity inserts.  Then import your data.  Then turn identity insert off for the table.
0
 
cb1393Commented:
To get rid of that error message, follow the instructions here:

http://msdn.microsoft.com/en-us/library/bb895146.aspx
0
 
cb1393Commented:
knight: I thought that might be the issue as well, but had to read the question again. It doesn't sound like an error is being generated when the insert occurs, which would happen if the column in question was indeed an identity column. Could be wrong, though.
0
 
souldjAuthor Commented:
Thanks for all the feedback but the winning comment solved ALL the issues.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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