Identity Seed

Can I change the value of a column's identity seed programmatically? If
so, how?

Thanks in advance.William Ortenberg <billort@.pacbell.net> wrote in message news:<v%FYa.2724$yF6.589@.newssvr22.news.prodigy.com>...
> Can I change the value of a column's identity seed programmatically? If
> so, how?
> Thanks in advance.

You can use DBCC CHECKIDENT with RESEED, but the new value is not
permanent. If you truncate the table, the seed will return to the
original value specified when you created the table.

If you want to change the seed value permanently, then you'll have to
recreate the table with the new seed specified in the column
definition.

Simon

create table dbo.t (
col1 int identity(1,1)
)

insert into t default values
select scope_identity() -- 1

dbcc checkident(t, RESEED, 5)

insert into t default values
select scope_identity() -- 6

truncate table dbo.t

insert into t default values
select scope_identity() -- 1

drop table dbo.t|||Hi William, The SET IDENTITY_INSERT command allows explicit values to
be inserted into the identity column of a table.

There are examples in the online help.

I have used this many times for migration of legacy data into SQL