I am working with VB.NET.. i have a DataTable called "QUESTION", containing 3 fields:
In my SQL Server database I created a Table called "QUESTION" with the same fields. QuestionNumber is defined as integer unique key, auto increment
Now, when i make a bulk copy to insert the DataTable into the SQL Server, the database overwrites my QuestionNumber from the DataTable and generates new ones (starting from 1 increment 1).
How do i have to change my database setup, that the original QuestionNumbers are copied into the database?
when just using inserting try:
SET IDENTITY_INSERT [your table] ON INSERT INTO [your table] (identityCol, col1,...) VALUES (identityCol, col1,...) SET IDENTITY_INSERT [your table] OFF
when bulk copying data you need add special parameter/switch/hint on the command, they are detailed here:
Look up IDENTITY INSERT. You turn it on. Update the table, then turn it back off.
SET IDENTITY_INSERT table ON
Please note that you can only have it on for one table at a time. If you turn it on for a different table, it turns off on the last table.
If you insert a value that is higher than the largest existing value, it will reseed itself to that value, so that all new values are greater.
Again, don't forget to turn it off:
SET IDENTITY_INSERT table OFF
If you're literally doing BULK INSERT, then don't forget the KEEPIDENTITY qualifier, which tells the server NOT to ignore your identity values, otherwise, it will ignore your identify values and generate new ones for the identity column.