How to Migrating two databases which using Primary key and auto increment value SQL 2008 R2

Question

I have 2 databases. Both contain same table designs DB1 and DB2. both having different data. I need to integrate DB2 to DB1. DB1 contain primary keys with auto increment. i need to insert DB2 datas to DB1 top. Means DB1 primary keys = top of DB2+ DB1 id.

ie, if DB1 contains 1,2,3,4,5 and DB2 also contains 1,2,3,4,5. when I insert data from DB2 to DB1, the data in DB2 will become 6,7,8,9,10. but I need 1,2,3,4,5 for DB2 and 6,7,8,9,10 as DB1 data..

Thanks in advance Jidhu

Answer 1

Try following query:

DECLARE @MaxId INT
SELECT @MaxId = MAX(Id) FROM Db1

SET IDENTITY_INSERT db1 ON
INSERT INTO Db1(Id, Column1, Column2, ...)
SELECT @MaxId+ ROW_NUMBER()OVER(ORDER BY Id),
        Column1,
        Column2,
        ...
FROM db2
SET IDENTITY_INSERT db1 OFF