Is there a way to get sql to "fill in the blanks" so to speak. I have a
database where the identity number field goes up to 100,000, although we
have no where near that many records. I have some new data I have to import
and they don't want them to be over 100,000. I tried creating a new table,
copying the old data numbers in, changing the field to an identity field,
then dumping in the new data but it always starts at 100,001.

I am using sql 2000

I'm not sure what you mean by "copying the old data numbers in" but creating
a new table with an identity column and then inserting everything but the
identity column should compute new identities. The problem with this is
that any other tables that use that identity value as a foreign key would be
broken by that - the new key value won't match the old value in the foreign
key. The only way to keep the related foreign keys in sync would be to
write a script that inserted the rows into the new table one at a time,
retrieved the new identity of the inserted row with SCOPE_IDENTITY() and
then updated all the foreign keys to the new identity. Be real sure you
have a good backup before you try this because the chances of getting it
right the first time are pretty slim. The real question is why the
application can accept numbers over 100,000. If you got your numbers up
that high once, it's going to happen again and fixing it is a real pain -
especially if the identity value is exposed somewhere - for example if the
identity value is used as a customer ID, are all your customers going to
have to get a new ID?

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

[quoted text, click to view]

The easiest way to find the gaps in an identity sequence is with an
auxiliary numbers table (there are other trickier queries that you could
use too but this is easier, and auxiliary tables are useful for all
sorts of things).

-- Create an auxiliary numbers table
create table #nums (n int primary key clustered)

-- Populate it with 100 rows (keep it small for the example)
declare @i int
set @i = 1
insert into #nums (n) values (1)
while (@i*2 < 100)
begin
insert into #nums (n) select n+@i from #nums
set @i = @i*2
end
insert into #nums (n) select n+@i from #nums where n+@i < 100

-- Simulate some table with an identity column
create table #foo (bar int identity(1,1), junk varchar(10) null)
set @i = 1
while (@i < 100)
begin
insert into #foo (junk) values ('blah'+cast(@i as varchar(4)))
set @i = @i + 1
end

-- Delete some rows (every 7th row) to make gaps
delete #foo where bar%7 = 0

-- Show the gaps in the identity sequence
select n from #nums as a
where not exists (select * from #foo as b where a.n = b.bar)
order by n

-- tidy up
drop table #nums
drop table #foo


You could also use a "not in ()" predicate

select n from #nums
where n not in (select bar from #foo)
order by n

but it'll give the exact same execution plan as the "not exists ()"
version (at least in this simple example...and I'm a creature of
habit). Obviously you'd make the numbers table permanent (rather then
the temp table I created for the example) so you don't have to keep
creating it and dropping it every time you want to use it for something
(and put a million or more INTs in it) and change #foo as appropriate.

You can also alter an identity column's current seed value with DBCC
CHECKIDENT
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_5lv8.asp> (see
http://www.aspfaq.com/show.asp?id=2237) but this is rarely necessary as
an identity column is purely a surrogate key (eg.
http://www.aspfaq.com/show.asp?id=2523) that has no meaning and as such
it never matters that there are gaps in the sequence (although there is
always much debate about identity columns).

Hop this helps.

--
*mike hodgson*
http://sqlnerd.blogspot.com



[quoted text, click to view]