0

I'm trying to copy identity column from one table to another, but I'm getting this error:

Cannot update identity column 'ID'.

I tried following code:

ALTER TABLE [dbo].[TableA]
    ADD [ID] INT IDENTITY(20000,1), -- MAX(TableB.Id) < 20000

SET IDENTITY_INSERT TableA ON

UPDATE TableA 
SET TableA.[ID] = TableB.[ID]
FROM TableA 
INNER JOIN TableB ON TableB.ID = TableA.ID

SET IDENTITY_INSERT TableA OFF;

Scenario

I have two tables with 1:0-1 relationship.

TableA: Code (PK)
A,
B,
C,
D

TableB: Id (PK), Code (Unique)
1, A
2, B
3, D

Question

How to

  1. Create new identity column TableA.Id
  2. Copy values from TableB.Id
  3. Ensure new unique values for TableA.Id if it was missing in TableB.Id
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Liero
  • 25,216
  • 29
  • 151
  • 297
  • 2
    As the error tells you, you can't `UPDATE` the value of an `IDENTITY`. You'll need to `CREATE` a new table, and `INSERT` the data into said new table with `IDENTITY_INSERT` enabled. Then you'll likely want to `DROP` your old table, and rename your new table. – Thom A Sep 21 '21 at 09:17
  • With foreign key constraints there is a lot to consider. See https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column. Plus there is no count of the rows involved. Under these circumstances will need to go through with a side table, replace the foreign key constraints, drop the old table and (sp_)rename the new table to old table. I am afraid this is tedious. – Knut Boehnert Sep 21 '21 at 15:42

1 Answers1

1

=NOT A REAL ANSWER TO THE ORIGINAL QUESTION, see comment=

You can not update a column which in itself is your join criteria. This just ain't work.

The safest way to keep the identity column intact is to delete rows from Table B in Table A first. Then just insert from Table B into Table A with IDENTITY_INSERT ON. Also this assumes there is a 1:1 relationship between the rows common to Table A and Table B. A 1:0-1 relationship between Table A and B means you need to capture ID from Table B in Table A as a foreign key relationship and separate column instead if this can lead to NULL results in Table A.

So in a nutshell (pseudo code) with assumption of 1:1 relationship between the rows:

DELETE FROM Table A WHERE Key EXISTS IN (SELECT Key FROM Table B)
;
SET IDENTITY_INSERT Table A ON
;
INSERT INTO Table A
(  id_column, [other_columns [, othercolumns] ] )
SELECT
  id_column, [other_columns [, othercolumns] ]
FROM Table B
;
SET IDENTITY_INSERT Table A OFF
;
Knut Boehnert
  • 488
  • 5
  • 10
  • Looks good on the first sigh, but DELETE wont work since I have many FK pointing to TableA. – Liero Sep 21 '21 at 09:43