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?