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

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?

Answer 1

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

Answer 2

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

Answer 3

OK, here is the code which solved my problem. Thank you Marcus and KM!

' set identity_insert to on to insert tables with key'
        Command = New SqlCommand("SET IDENTITY_INSERT table ON", con)
        Command.ExecuteNonQuery()


'Copy dataTable into MSSQL database'
 Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(cn, SqlBulkCopyOptions.KeepIdentity)
            bulkCopy.DestinationTableName = dTable.TableName
            Try
                bulkCopy.WriteToServer(dTable)
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using

        'disable identity_insert'
        Command = New SqlCommand("SET IDENTITY_INSERT table OFF", con)
        Command.ExecuteNonQuery()