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
using (
	select
		old.id,
		old.title,
		old.createdOn
	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)
		values(src.title, src.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.