Insert from another table, then update using Identity

Question

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.

Answer 1

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

Answer 2

Try something like this

SET NOCOUNT ON

--Initial setup
IF OBJECT_ID('dbo.x', 'U') IS NOT NULL
    DROP TABLE dbo.x;

IF OBJECT_ID('dbo.SomeData', 'U') IS NOT NULL
    DROP TABLE dbo.SomeData;
GO

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

INSERT INTO dbo.x (id,SomeData)
VALUES (1,1),(2,2)
GO

--Add SomeDataId column
ALTER TABLE dbo.x ADD SomeDataId BIGINT
GO

--Create new SomeData table with an extra column
--to hold the x.Id column value for each row
CREATE TABLE [dbo].[SomeData] (
    [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY
    ,[Binary] VARBINARY(MAX) NOT NULL
    ,[XId] TINYINT
    )

--insert dbo.x data into dbo.SomeData
INSERT INTO dbo.SomeData (BINARY,XId)
SELECT SomeData,Id
FROM dbo.x
GO

--Update dbo.x.SomeDataId with the identity value
--generated on the dbo.SomeData.Id column
UPDATE x
SET x.SomeDataId = n.Id
FROM dbo.x
JOIN dbo.SomeData n ON n.xid = x.id
GO

--Drop the dbo.x.SomeData column
ALTER TABLE dbo.x
    DROP COLUMN SomeData
GO

--Drop the dbo.SomeData.Xid column
ALTER TABLE dbo.SomeData
    DROP COLUMN XId
GO

SELECT * FROM dbo.x

SELECT * FROM dbo.SomeData

Answer 3

I determined that I probably cannot do it without some kind of mapping table.

My final solution is to store the mapping in a temp table as an intermediate step. To do the insert, I used the Merge statement with condition 1=0 to force an insert.

CREATE TABLE #Mapping(
    XId TINYINT,
    SomeDataId BIGINT
);

MERGE INTO SomeData USING X ON 1=0
WHEN NOT MATCHED THEN
    INSERT ([Binary])
    VALUES (X.SomeData)
    OUTPUT X.Id, INSERTED.Id INTO #Mapping;


UPDATE X
SET [SomeDataId] = #Mapping.SomeDataId
FROM #Mapping
WHERE #Mapping.XId = X.Id

DROP TABLE #Mapping;