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.