Capturing identity ID's from an insert or update

So you need to insert data into multiple columns, all linked with identity columns for primary keys.  You need to be able to insert into one table, and pass on the value from the identity column to the foreign key of another table.  You can't use @@IDENTITY or SCOPE_IDENTITY() unless you do so in an implicit cursor, and I've seen plenty of this, but cursors like this are slow and cumbersome... and high on resources.  Such a script could take hours to run when writing it as a set based solution could run in a mere fraction of the time. Let's assume you want to avoid your script running like a tortoise through a pool molasses... so we won't be focusing on cursors.

Of course the data coming over will have ID's.  You could create a new column to store that ID, but then you're changing the schema and the developers might not be too happy about that.  You're also adding redundant data that you may forget to remove, and taking up more disc space than you need to.  I've done this when starting out myself, but there is a better way.

Use the OUTPUT clause to log to a temporary table, including the generated identity numbers in thew system as well as those of the old one, allowing you to map the old ID's to the new ID's.  A temporary table will persist for only as long as your session, so you don't have to worry about it taking up any disc space.  You won't change any schema's and it's pretty fast.  HOWEVER, it's important to note that a normal insert statement will not return the ID's of the source table to the output clause.  Output requires an inserted or deleted value from the destination table, or for the the column to be contained in a from clause belonging to the insert statement.  To do this we can use the MERGE statement.

Here is an example scenario.  The NSA are keeping track of our phone calls, who the caller and receiver is and if it's a call of interest.  They need to migrate this information out of their PRISM system into their new uber secret one running with SQL Server 2012, called TipTop.  We have a table for phone numbers and a call table that logs calls between these numbers with a flag if the call is of interest to the NSA.

Lets first create the tables we need, and populate the old PRISM tables with data.

--CREATE THE TABLES
create table #PrismNumbers(
NumberID int identity (100,1),
Number varchar(12),
BillPayerSurname varchar(25)
);

create table #PrismCalls(
CallID int identity(100, 1),
CallerID int,
ReceiverID int,
OfInterest char(1)
);

create table #TipTopNumbers(
TTNumberID int identity(200,1),
TTNumber varchar(12),
TTBillPayerSurname varchar(25)
);

create table #TipTopCalls(
TTCallID int identity(200, 1),
TTCallerID int,
TTReceiverID int,
TTOfInterest char(1)
);

create table #IDMap(
TTID int,
PrismID int
);

--POPULATE THE TABLES WITH DATA
insert into #PrismNumbers(Number,BillPayerSurname) values
('01999 897786','Smith'),
('01998 564453','Jackson'),
('01997 786675','White'),
('01996 231122','Johnson'),
('01999 345679','Williams');

insert into #PrismCalls(CallerID,ReceiverID,OfInterest) values
(100,104,'N'),
(103,102,'N'),
(101,104,'Y'),
(103,101,'Y');

Ok... now that we have our tables in place, we need to start our migration.  We first need to migrate the numbers, outputting to the IDMap table so we can insert the correct new ID's for the TipTop system.  This is where we use the merge statement:

merge into #TipTopNumbers as tt
using #PrismNumbers as p
on 1=2 --<-- Merge condition that will always be false
when not matched
then insert (TTNumber,TTBillPayerSurname) 
values (p.Number,p.BillPayerSurname)
output inserted.TTNumberID as TTIDs,p.NumberID as PrismIDs
into #IDMAP;


Now try "select * from #IDMap" to see the mappings of ID's  Now we can finally migrate the calls that reference the new TipTop number table.

insert into #TipTopCalls(TTCallerID,TTReceiverID,TTOfInterest)
select cm.TTID,rm.TTID,c.OfInterest
from #PrismCalls c
join #IDMap cm on cm.PrismID = c.CallerID
join #IDMap rm on rm.PrismID = c.ReceiverID;

And there you go.  You've now migrated data from Prism to TipTop without bringing over the old ID's. Bear in mind though that Merge is only available from 2008.  Happy migrating!

*Edit 30/03/2014*
I should mention more on how the Merge statement is used here.  Of course this isn't the intended use. We're just using it to do inserts in an ANSI standard format that allows us to use the output clause in the way we need.  To do this we ensure that the condition will always be false with a predicate of 1=2.  If you want to apply joins or a more complex query to define the source data, you can use a common table expression (CTE).

*Edit 31/03/2014*
Of course, maybe you don't need to output to a temporary table.  You could output directly the table you intend to migrate to.