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; |
