Duplicate records with identity column

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 an开发者_开发百科y way to copy the data properly without involving cursors?

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)

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