TSQL: OUTPUT Columns Not In Target Table

One of the challenges I’ve run into regularly throughout my years trying to convert data from one system to another is that we have a set of old data, often with its own key values. That data is mapped into a new system with different key values and often no place to store a mapping between the old and new systems.  SQL Server 2005 introduced the OUTPUT clause – a convenient way to insert, update, or delete data in a table and track the affected rows in a table variable or temp table.  The problem was that this still doesn’t provide a way to map the old and new values if there are no columns in the target table for those old values.

I considered pre-generating tables using the SEQUENCE functionality, then using an IDENTITY INSERT to populate the new tables.  This presented a couple of challenges. The SQL for the SEQUENCE had to be generated because you can’t pass in a variable for the sequence. You also need to have permissions to modify the schema to do an Identity RESEED.  For the same reason, I wasn’t able to create a temp table w/ an IDENTITY column and reseed it, at least not in Azure SQL.

After doing some research, I came across a hint that someone had written up some code on using MERGE along with the OUTPUT clause to insert new values into a table while also providing a way to use columns that don’t exist in the target table.  That person ended up being Adam Machanic (b | t), a respected name in the SQL Server community. (You may have used his “sp_whoisactive” code in the past if you’ve worked with SQL Server. If not, you probably should.)

You can read the original post here, but I’ll try to summarize to a quick “how to” using Adventureworks.

Below is some sample code that can be run against the Adventureworks Database.


–Create test table to represent data we want to import.
CREATE TABLE #ProductsToImport (
OriginalSystemID INT,
ProductName NVARCHAR(50),
ProductNumber NVARCHAR(25),
SafetyStockLevel SMALLINT,
ReorderPoint SMALLINT,
StandardCost MONEY,
ListPrice MONEY,
DaysToManufacture INT,
SellStartDate DATETIME
);
INSERT #ProductsToImport
(
OriginalSystemID,
ProductName,
ProductNumber,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
DaysToManufacture,
SellStartDate
)
VALUES
(-1, N'My Test Product 1', N'TS-1111', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-2, N'My Test Product 2', N'TS-2222', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-3, N'My Test Product 3', N'TS-3333', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-4, N'My Test Product 4', N'TS-4444', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-5, N'My Test Product 5', N'TS-5555', 10, 50, 0.00, 0.00, 1, GETUTCDATE());
–Create table to track the mapping of the original ID to the new ProductID
CREATE TABLE #ProductMapping (
OriginalSystemID INT,
ProductID INT
);
–Import into the Products table, storing the Old/New IDs in the #ProductMapping table.
;
MERGE INTO Production.Product AS p
USING (
SELECT pti.OriginalSystemID, –Note that this column will not be inserted, but we can fetch the value later.
pti.ProductName,
pti.ProductNumber,
pti.SafetyStockLevel,
pti.ReorderPoint,
pti.StandardCost,
pti.ListPrice,
pti.DaysToManufacture,
pti.SellStartDate
FROM #ProductsToImport AS pti
) AS src
ON 1 = 0 –want to make sure we never get a match in this case
WHEN NOT MATCHED
THEN INSERT(Name, ProductNumber, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate )
–use the columns in the above "src" query
VALUES (
src.ProductName,
src.ProductNumber,
src.SafetyStockLevel,
src.ReorderPoint,
src.StandardCost,
src.ListPrice,
src.DaysToManufacture,
src.SellStartDate
)
–Now use the OUTPUT statement to get the OriginalID from the "src" query
–and the newly created Identity value from the Product table
–Insert those into the #ProductMapping table
OUTPUT src.OriginalSystemID, inserted.ProductID
INTO #ProductMapping (OriginalSystemID, ProductID);
–Verify results in mapping table
SELECT pm.OriginalSystemID, pm.ProductID
FROM #ProductMapping AS pm;

Leave a Reply

Your email address will not be published. Required fields are marked *