Append data from one table to another retaing identity sequence

Transact-SQL
1

Hi folks,

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)
ID Client
1 100
3 200

Table 2
ID Client
1 10
2 11
3 12
4 13

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:
Table 2
ID Client
1 10
2 11
3 12
4 13
5 100
7 200

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!

2

EXEC('INSERT INTO Db2.dbo.TheTable (ID, Client) SELECT ID + (Select Max(ID) From Db2.DBO.TheTable), ' +

4

Hi folks,

Come to think of it, I provided too much information. Suffice it to say, I'm trying to do this:

SET IDENTITY_INSERT Db2.dbo.TheTable ON
INSERT INTO Db2.dbo.TheTable SELECT ID, Client From Db2.DBO.TheTable ORDER BY ID
SET IDENTITY_INSERT Db2.dbo.TheTable OFF

And this does not work. I still get "An explicit value for the identity column in table 'PatientsSQL.dbo.Comments' can only be specified when a column list is used and IDENTITY_INSERT is ON." Anyone have any idea why?

5

You must specify the column list in the INSERT statement:

INSERT INTO Db2.dbo.TheTable (ID, Client) SELECT ID, Client From Db2.DBO.TheTable ORDER BY ID

But I think you have a deeper problem (and a typo)...you cannot insert duplicate values. If you can insure that the values coming from DB2 do not exist in the table on DB1, then the insert will work. However - it seems you are trying to update the identity value in DB1 at the same time as inserting the new values from DB2 which won't work.

Typo: I think you meant: INSERT INTO db1.dbo.TheTable...

6

Ha! Hadn't noticed that you have to specify the column list in both the front and back ends of the Insert Into Select statement. Thanks Jeff!

P.S. I did ensure that there are no ID duplicates. Thanks!