Duplicate records with identity column

Question

Consider the following design:

Company TABLE (
    CompanyId int NOT NULL IDENTITY PRIMARY KEY,
    CompanyName nvarchar(max))
Product TABLE (
    ProductId int NOT NULL IDENTITY PRIMARY KEY,
    CompanyId int NOT NULL,
    ProductName nvarchar(max))
ProductPart TABLE (
    ProductPartId int NOT NULL IDENTITY PRIMARY KEY,
    ProductId int NOT NULL,
    ProductPartName)

I need to duplicate a company (with CompanyId == @companyId) data so it will contain exactly the same data.

The first step is obvious:

INSERT INTO Company(CompanyName)
SELECT @newCompanyName
FROM Company
WHERE CompanyId = @companyId

DECLARE @newCompanyId = SCOPE_IDENTITY()

Without ProductPart table it would be trivial to copy Product data as well:

INSERT INTO Product (ProductName)
SELECT ProductName
FROM Product
WHERE CompanyId = @companyId

But in order to duplicate ProductPart data properly I need to have product mapping data between old and new companies.

I tried to use OUTPUT clause but unfortunately it doesn't support my scenario:

DECLARE @productRemap TABLE (OldProductId int NOT NULL, NewProductId int NOT NULL PRIMARY KEY)
INSERT INTO Product (ProductName)
    OUTPUT ProductId, inserted.ProductId
        INTO @productRemap
SELECT ProductName
FROM Product
WHERE CompanyId = @companyId
-- Invalid column name 'ProductId'.

Is there any way to copy the data properly without involving cursors?

If you use SQL Server 2008 you could use `merge` instead. Have a look at this question http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id. — Mikael Eriksson, Jul 04 '11 at 09:46

score 2 · Accepted Answer · answered Jul 04 '11 at 10:29

Thanks to Mikael I found that I can actually use MERGE instead of INSERT:

DECLARE @productRemap
    TABLE (OldProductId int NOT NULL, NewProductId int NOT NULL)

-- Duplicate products
MERGE INTO Product AS t
USING (
    SELECT *
    FROM Product
    WHERE CompanyId = @companyId) AS s
ON (0 = 1)
WHEN NOT MATCHED THEN
    INSERT (ProductName, CompanyId)
    VALUES (ProductName, @newCompanyId)
    OUTPUT s.ProductId, inserted.ProductId
        INTO @productRemap;

Then I can insert into ProductPart using mapping from @productRemap:

MERGE INTO ProductPart AS t
USING (
    SELECT * FROM ProductPart
        INNER JOIN @productRemap ON ProductId = OldProductId) AS s
ON (0 = 1)
WHEN NOT MATCHED THEN
    INSERT (ProductId, ProductPartName)
    VALUES (NewProductId, ProductPartName)

score 0 · Answer 2 · answered Jul 04 '11 at 09:39

0

I understand you're looking to do this strictly with TSQL, however, form experience I have found it easiest to add an OldProductId field to the Product table and map Product Parts from that:

INSERT INTO Product (ProductName, OldProductId)
SELECT ProductName, ProductId
FROM Product
WHERE CompanyId = @companyId

answered Jul 04 '11 at 09:39

Barry Kaye

7,682
6
42
64

Unfortunately I can't add any additional columns to `Product` table – Regent Jul 04 '11 at 09:44

Duplicate records with identity column

2 Answers2