3

In a C# program I need to copy data from my database to a 3rd party database. I am generating a customer number and inserting that (with other data) into the 3rd party database.

I am the only person writing to that table. Their database is already built and they can't/won't rebuild the table. So, they have an auto increment set up on their table (qbCUST). I need to bypass this.

I have found the following post How to put SET IDENTITY_INSERT dbo.myTable ON statement. I am a bit unclear on the usage. The accepted anwser is this code snippet:

using (var connection = new SqlConnection("Connection String here"))
{
    connection.Open();
    var query = "SET IDENTITY_INSERT dbo.MyTable ON; INSERT INTO dbo.MyTable (IdentityColumn) VALUES (@identityColumnValue); SET IDENTITY_INSERT dbo.MyTable OFF;";

    using (var command = new SqlCommand(query, connection)
    {
        command.Parameters.AddWithValue("@identityColumnValue", 3);
        command.ExecuteNonQuery();
    }
}

Should I not be turning identity insert OFF first?

Community
  • 1
  • 1
Gavin
  • 437
  • 2
  • 8
  • 20

1 Answers1

8

No; SET IDENTITY_INSERT ... ON means that you are taking control of the IDENTITY; setting it OFF means that you are handing control back the database. It is correct as written. The default is OFF which means that you cannot insert into IDENTITY columns.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900