Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
You can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LXYou can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>