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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Scott PletcherSenior 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 )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.