Bringing together 2 Access databases - Autonumber issue 1

[litsz02]

I'm working on getting data loaded into a database table on SQL server.

I've got 2 Access 2000 databases - 1 has 2003 information in it and the other has 2004-05 info in it. I was having some issues with combining the two, mainly because both Access databases use an Order Number field which is Autonumbered as the Primary key. There aren't any duplicate key issues, it just will not let me bring them over without completely redoing the Autonumber in SQL server, because that's how the SQL server table is set up. When I try and combine the two access databases into one, it redoes the autonumbering as well.

Basically, what i need is to bring over 2003 data (ex. Order Numbers 1-1000) and then 2004-05 data (Order Numbers 1001-2000) and then continue the autonumber from there (2006 would start at 2001-3000 and so on). When I tried to combine, it just completely restarted the autonumber on me. Do you have any suggestions on how to get this working?

 

[jhall01]

This may sound like a round about way to do it, but could you export the two tables to a TXT file then import into SQL that way so you maintain the autonumber field?

 

[SQLSister]

If you are sure the numbers are different between the two tables existing now, then you can create the table with the structure you want , then use SET IDENTITY_INSERT MyTable ON. Then run the insert SQL for table1, then do it for table2, then run SET IDENTITY_INSERT MyTable OFF.

What this does is allow the manual insertion of numbers for the identity field. This will work fine as long as you have different data in each of the two tables you are getting information from.

If there are records with the same number in both tables, then you need to create a new field for the idnetity and then use your new ID in conjunction with the old one to change the values in any related tables before importing them to the new database.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[litsz02]

I had actually tried to export to txt file before, but when i put imported it, that was when it screwed up the autonumber field.

I tried the Set Identity Insert and it worked perfectly. Thanks so much for both of your quick responses!