How to insert a DataTable with existing Key to a SQL Server Table

database datatable sqlbulkcopy sql-server vb.net

Question

I am working with VB.NET.. i have a DataTable called "QUESTION", containing 3 fields:

  • QuestionNumber (unique integer key)
  • QuestionText
  • QuestionType

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?

Accepted Answer

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:

Keeping Identity Values When Bulk Importing Data


Popular Answer

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.

Keeping Identity Values When Bulk Importing Data




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why