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...
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...
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
1. it needs SELECT IDENT_CURRENT('dbo.your_ta ble_name') + 1
2. I did not take answer why suddenly...
2. I did not take answer why suddenly...
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