Sql-server – Insert from another table, then update using Identity

sql servert-sql

I am changing the database schema and migrating data that was originally stored in one table to two tables.

Old schema:

CREATE TABLE [dbo].[X](
    [Id] TINYINT NOT NULL PRIMARY KEY, 
    [SomeData] VARBINARY(MAX) NULL, 
)

New schema:

CREATE TABLE [dbo].[X](
  [Id] TINYINT NOT NULL PRIMARY KEY, 
  [SomeDataId] BIGINT, 
)
CREATE TABLE [dbo].[SomeData](
  [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY,  
  [Binary] VARBINARY(MAX) NOT NULL
)

I know the steps are:

  1. Create the new column [SomeDataId] on [X]
  2. Copy data from [X].[SomeData] to [SomeData].[Binary]
  3. Remove column [SomeData] on [X]

I am having issue on how to complete step 2 in T-SQL only. I know I can:

  • Use SCOPE_IDENTITY() to get the inserted identity seed
  • Use OUTPUT INSERTED.Id to get the inserted identity seed
  • Use INSERT INTO ... SELECT ... FROM to copy data from one table to another

However I cannot figure out how to update the relevant row on [X] with the identity seed just inserted, without using some kind of for-loop logic which sounds silly.

Best Answer

  1. While creating column SomeDataId add identity

    alter table dbo.x add SomeDataId BIGINT Identity(1, 1)

  2. Then enable identity_insert on table SomeData and insert rows

    SET IDENTITY_INSERT dbo.SomeData ON insert into SomeData(id, binary) select SomeDataid, SomeData from x

Related Question