I thought I’d share some SQL knowledge I picked up today. I’ve used this before but I figure I should publish it as writing it down helps me retain it and someone else may find it useful.
So, I needed to move some data from one table to another which is pretty straight forward. I also needed to output the id of the newly created record so that I could insert it into another table. This is pretty simple with an output statement. However, I also needed the id from the source table that I was pulling the data from. Basically, I needed an old id/new id mapping between the tables.
There are two ways I could go about this. I could change the schema of the target table to have a column to hold the old id and then insert everything in. This seems too intrusive though. There are lots of reasons why this is a bad idea but the best reason is that there is a better way.
The key is a merge statement. If you haven’t familiarized yourself with a merge statement, do yourself a favor and learn it. The syntax can be a little odd at first but its very powerful. All I needed to do was to include the source id in my merge statement and then I can output it in to my mapping. I’ve provided some code below as an example.
First, I declare my mapping table. Pretty straightforward, just a table variable.
declare @map table(
id_old int not null,
id_new int not null
Next, I do the merge statement.
merge DESTINATION_TABLE as tgt
from SOURCE_TABLE old
) as src(id, title, createdOn)
/* Never match. I only do this because I'm always performing an
* insert. Depending on the requirements of your merge, you can
* change this line to whatever suits you. */
on (1 = 0)
when not matched by target then
insert (title, createdOn)
output src.id, inserted.id into @map
The key to the merge statement is to include the old id in my source block: as src(id, title, createdOn). I’m not required to use the old id in the insert statement but I do have access to it in the output statement. I also have access to all the values that were inserted.
There you have it. A pretty simple way to get a mapping of the old and new ids into one table.