I am changing the database schema and migrating data that was originally stored in one table to two tables.
CREATE TABLE [dbo].[X]( [Id] TINYINT NOT NULL PRIMARY KEY, [SomeData] VARBINARY(MAX) NULL, )
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:
- Create the new column [SomeDataId] on [X]
- Copy data from [X].[SomeData] to [SomeData].[Binary]
- Remove column [SomeData] on [X]
I am having issue on how to complete step 2 in T-SQL only. I know I can:
SCOPE_IDENTITY()to get the inserted identity seed
OUTPUT INSERTED.Idto get the inserted identity seed
INSERT INTO ... SELECT ... FROMto 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.