It looses the seed from auto increment field when import data

I use the import wizard in sql server 2008 R2 to import data from excel 2007 to a table with an auto increment column. But when I use the option "delete rows in destination table" then reset the auto increment column to 1... I do not use "Enable identity insert".  This is a BIG problem...
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
You can still use TRUNCATE to delete the old rows, which generates much less overhead than a DELETE, while retaining the existing identity value.

Run the code below immediately before you need to import new data.  Then select "Append" rather than "Delete rows" when copying in data.

DECLARE @reseed_identity_value int
SET @reseed_identity_value = IDENT_CURRENT('dbo.your_table_name') + 1

TRUNCATE TABLE dbo.your_table_name
DBCC CHECKIDENT ( 'dbo.your_table_name', RESEED, @reseed_identity_value )
hi nonlinearly,

Let's take one step back.  What is it that needs to be accomplished i.e. the business requirement of this?

If you could perhaps shortly explain the process that is needed.

Brian CroweDatabase AdministratorCommented:
When you select the "delete rows in destination table" it is running a TRUNCATE TABLE command which resets the IDENTITY seed value.  This is generally a good thing since TRUNCATE runs much faster than a DELETE since it is deleting records at the page level.

Empty the table yourself using "DELETE FROM <tablename>" and then run the import wizard with the "append" option.

nonlinearlyAuthor Commented:
Yes I know... but in sql server 2000 I had not this behaviour... and worst... the same for sql server 2008 r2 until now (suddenly!!!)... WHY
nonlinearlyAuthor Commented:
1.  it needs SELECT IDENT_CURRENT('dbo.your_table_name') + 1
2. I did not take answer why suddenly...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.