Reseeding Identities

I did a job today where I needed to to copy data from one table to another.  Both tables were exactly the same, with identity columns and the destination table empty.  In auto-pilot mode, I was about to write a script to copy the data across, capture the new identity numbers to have them mapped to the old one (much like in my previous post Capturing identity ID's from an insert or update).  But then of course I quickly realised that there was a much simpler way of doing this.  It didn't matter if the identity numbers were contiguous. I could just switch identity insert on, and retain all the values as they were, which also made it easier when I copied across data from other tables that linked on foreign keys.

A couple of things to take note of though, when doing this:

1. You can't just insert a value into an identity column.
2. When you DO insert values into an identity column, the values will be higher than the next number to be generated, which will fail due to a unique constraint.

So, you first need to enable identity insert.  This will, wait for it... allow you to insert values into an identity column. Duh, right?  Just make sure you switch it off again when you're done.  This is the statement that you need to run:

SET IDENTITY_INSERT {table_name} ON | OFF;

Another thing to be aware of in order to insert... you need to specify all of the column names in the insert statement.  There's no using select *, or relying on the order of columns in the destination  table.

So you've set identity_insert to on and inserted the data, probably using insert into select.  Remember that the value of the next identity number to be generated won't be affected by the insert and so will need to be changed.  This is done using the DBCC CHECKIDENT command, which can also be used to see what the current identity value is.  To reseed though, you will run the following:

DBCC CHECKIDENT ({table_name}, RESEED, n);

Where n is value of the next number you want to have generated minus 1.  So if the highest value of the records you've inserted is 666, then you will reseed the column to be 666, and the next number it generates will be 667.

---------------------
--Reseed Example--
---------------------
declare @maxID int;

set identity_insert destinationTable on; 

insert into destinationTable (ID, col1, col2, col3)
select (ID, col1, col2, col3)
from sourceTable; 

set identity_insert destinationTable off; 

select @maxID = max(ID) from destinationTable; 

DBCC CHECKIDENT (destinationTable, reseed, @maxID);

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.