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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| –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; |
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.