Legacy Data and Identity Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Legacy Data and Identity Column

Author

Topic

takkone
Starting Member

8 Posts

Posted - 2011-06-01 : 14:44:13

I'm migrating a quote database from an old non-SQL system to a new SQL Server database. Some of my legacy data table have an identity column, and because of deletions over the years there are some "holes" in the sequence. I'd like to import the data and KEEP the old identity values, and then "reset" the counter to a number higher than any existing, so moving forward the new database can incremnet those identity fields where the old system left off. Should I remove my identity specification on my new tables first, then put it back after importing my data? then how do I "bump" up the counter for the next value?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 14:54:37

This might help

create table #t (id int identity (1,1),col1 int)
insert into #t
select 2 union
select 4

select * from #t

SET IDENTITY_INSERT #t ON -- << read about SET IDENTITY_INSERT in Books On Line
INSERT INTO #t(id,col1)
SELECT 5,1

SET IDENTITY_INSERT #t OFF
INSERT INTO #t
select 7

select * from #t

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 17:42:18

To add to what Jim said, after you have inserted the data, to "bump" the next identity value:

First find the largest value of the identity column.

Then use DBCC CHECKIDENT to set the next value as one more than the max value. http://msdn.microsoft.com/en-us/library/ms176057.aspx for example

DBCC CHECKIDENT ("YourTable", RESEED, 19230);

takkone
Starting Member

8 Posts

Posted - 2011-06-01 : 20:49:39

quote:
Originally posted by jimf

This might help

create table #t (id int identity (1,1),col1 int)
insert into #t
select 2 union
select 4

select * from #t

SET IDENTITY_INSERT #t ON -- << read about SET IDENTITY_INSERT in Books On Line
INSERT INTO #t(id,col1)
SELECT 5,1

SET IDENTITY_INSERT #t OFF
INSERT INTO #t
select 7

select * from #t

Jim - Can you explain your use of numbers in the above sql statements? For example "SELECT 5,1" and "SELECT 2 UNION SELECT 4". I don't understand what those numbers are for.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-01 : 22:50:03

Why not fix this mess and get rid of the IDENTITY property, so you can use a proper, relational key instead? If your old car had flat ties, would you move them to your new car?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-02 : 13:15:08

quote:
Originally posted by jcelko

Why not fix this mess and get rid of the IDENTITY property, so you can use a proper, relational key instead? <snip>

If SQL Server was a true relational database I'd say you might be on to something. Since it is not, and as painful as it is, we surrogate the physical model because that's what SQL Server does well.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-02 : 13:30:11

Ahhh, so Lamprey is really Chris Date.