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.

• ypercubeᵀᴹ: Why is SomeDataId defined as a BIGINT when table X can have no more than 256 rows? And why do you even need a new id column when you can use X (Id)?
• kevin: @ypercubeTM because SomeData holds data for a lot of tables. I discovered that X, Y, Z etc. in my database has SomeData, and I want to move them to a central table.

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

• kevin: If I use this approach, the identity seed comes from X.SomeDataId. What if I want the identity seed to comes from SomeData.Id?

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;