0

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.

2
  • 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)? Oct 31 '17 at 14:04
  • 1
    @ypercubeᵀᴹ 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.
    – kevin
    Oct 31 '17 at 23:53
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

1
  • If I use this approach, the identity seed comes from X.SomeDataId. What if I want the identity seed to comes from SomeData.Id?
    – kevin
    Oct 31 '17 at 11:00
1

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
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.