How to insert a DataTable with an existing key into a SQL Server table

Question

I work 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 an integer unique key, auto increment

Now, when I make a bulk copy to insert a DataTable into SQL Server, the database overwrites my QuestionNumber from the DataTable and generates new ones (starting from 1 increment 1).

How can I change the database setting so that the original question numbers are copied to the database?

Answer 1

when just paste try:

SET IDENTITY_INSERT [your table] ON
INSERT INTO [your table] (identityCol, col1,...) VALUES (identityCol, col1,...)
SET IDENTITY_INSERT [your table] OFF

when you need to add a special parameter / switch / tooltip in a command to bulk copy data, they are described in detail here:

Saving Identification Values ​​for Bulk Data Import

Answer 2

Find IDENTICAL INSERT. You turn it on. Refresh the table, then turn it off.

SET IDENTITY_INSERT table ON

Please note that you can only use it for one table at a time. If you enable it for another table, it will be disabled in the last table.

If you insert a value that is greater than the largest existing value, it will succumb to the value so that all new values ​​are larger.

Again, be sure to turn it off:

SET IDENTITY_INSERT table OFF

BULK INSERT, KEEPIDENTITY, , .

Answer 3

OK, here is the code that solved my problem. Thanks 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()