Link to home
Start Free TrialLog in
Avatar of nonlinearly
nonlinearly

asked on

It looses the seed from auto increment field when import data

Hi,
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...
Avatar of wynandkunkel
wynandkunkel
Flag of South Africa image

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.

W
Avatar of Brian Crowe
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.

TRUNCATE TABLE:
http://technet.microsoft.com/en-us/library/aa260621(v=sql.80).aspx
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nonlinearly
nonlinearly

ASKER

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
1.  it needs SELECT IDENT_CURRENT('dbo.your_table_name') + 1
2. I did not take answer why suddenly...