Combining SQL Databases

dthansen
dthansen used Ask the Experts™
on
We have to take DatabaseA, merge all tables from Database B to make a new combined Database C.

The only issue I have is that both Database A and Database B use identity() to generate unique record identifiers used throughout the system.

There are only a few dozen tables in Database A so I was planning on re-creating the table structures in Database C and then using the simple 'select' statement to copy the source data into the new empty table. The issue is the identity() column from the source tables. Can I just set IDENTITY INSERT ON, do the select/copy and then shut IDENTITY INSERT off again?

If so, with the new table properly re-seed the next ID or do I have to do that manually somehow?

Thanks,
Dean
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
Sure, you can set the identity insert off, do the insert, and then back to on again.  
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Just to complement chapmandew post. For reesed you just need to run this command:
DBCC CHECKIDENT ( table_name)

Author

Commented:
I find the documentation on the DBCC CHECKIDENT command to be a little unclear.

After we copy across, we need to leave the identity values we copied in place, but just make sure the next row inserted begins counting from where our copied values left off.

The documentation leads me to believe the RESEED parameter might change the values we copied into the table.

Please advise.

Thanks,
Dean
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
RESEED doesn't changes values. Will only calculate the next value to be used.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial