I have 2 identical tables in 2 different databases which I need to merge. So the contents of the table in DB1 will go into the table of DB2. However, I must make sure that the identity sequence of DB1 stays as is, only incremented. For example:
Table 1 (where ID = 2 does not exist)
Once the merge is completed into table 2, added records should have IDs which are equal to their original IDs + the maximum ID of table 2 before merge. So, it should look like:
To do this, I figured that I would compose the following:
SET IDENTITY_INSERT Db2.dbo.TheTable ON
DECLARE @ColumnList varchar(Max)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name From INFORMATION_SCHEMA.Columns WHERE table_name = 'TheTable' AND column_name <> 'ID' ORDER BY ordinal_Position
EXEC('INSERT INTO Db2.dbo.TheTable SELECT ID + (Select Max(ID) From Db2.DBO.TheTable), ' + @ColumnList + ' FROM Db1.dbo.TheTable ORDER BY ID')
SET IDENTITY_INSERT Db2.dbo.TheTable OFF
But I keep getting the following error message: An explicit value for the identity column in table Db2.dbo.TheTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I don't get it. What am I missing? Thanks!