asked on

Combining SQL Databases

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

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dthansen

ASKER

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ão

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