Difference in behavior for SET IDENTITY_INSERT from SQL Server 2000 to 2005?

Question

I have an application that (currently) needs to uses DBs served by both SQL Server 2000 and SQL Server 2005.

I'm trying to move a DB programmatically from one server to the other. All of the tables I use each have a primary key that is autoincremented. Since these autoincremented keys are used as foreign keys in other tables, my plan of attack for copying the tables was to temporarily turn off the auto-increment on the destination table, INSERT the records verbatim (with the original primary keys) and then turn auto-increment back on.

If I understand correctly, I can "turn auto-increment on/off" one table at a time by executing

SET IDENTITY_INSERT tbl_Test ON;
...
SET IDENTITY_INSERT tbl_Test OFF;

My implementation is in VB 2005, using System.Data.SqlClient.

For brevity's sake, I'll only show the lines of code related to the construction of the SQL queries.

To create the table:

    Dim createTableStatement As String = _
        "CREATE TABLE tbl_Test (" & _
            "ID_Test INTEGER PRIMARY KEY IDENTITY," & _
            "TestData INTEGER" & _
            ")"
    Dim createTableCommand As New SqlCommand(createTableStatement, connection)

    createTableCommand.ExecuteNonQuery()

To turn IDENTITY_INSERT on or off:

    Dim turnOffIdentInsertStatement As String = _
        "SET IDENTITY_INSERT tbl_Test OFF;"
    Dim turnOffIdentInsertCommand As New SqlCommand(turnOffIdentInsert, connection)
    turnOffIdentInsertCommand.ExecuteNonQuery()

    Dim turnOnIdentInsertStatement As String = _
        "SET IDENTITY_INSERT tbl_Test ON;"
    Dim turnOnIdentInsertCommand As New SqlCommand(turnOnIdentInsert, connection)
    turnOnIdentInsertCommand.ExecuteNonQuery()

To insert a new record normally, without specifying primary key:

    Dim insertRegularStatement As String = _
        "INSERT INTO tbl_Test (TestData) VALUES (42);"
    Dim insertRegularCommand As New SqlCommand(insertRegularStatement, connection)
    insertRegularCommand.ExecuteNonQuery()

To insert a new record with an explicit primary key:

    Dim insertWithIDStatement As String = _
        "INSERT INTO tbl_Test (ID_Test, TestData) VALUES (20, 42);"
    Dim insertWithIDCommand As New SqlCommand(insertWithIDStatement, connection)
    insertWithIDCommand.ExecuteNonQuery()

So, my plan is to call the code that turns IDENTITY_ INSERT on, add the records with the explicit primary keys, then turn IDENTITY_ INSERT off.

This method appears to work fine when I'm talking to SQL Server 2000, but I run into an exception when I try the exact same code against SQL Server 2005:

"Cannot insert explicit value for identity column in table 'tbl_Test' when IDENTITY_INSERT is set to OFF."

Any hints? Thanks in advance as always.

Answer 1

You might have to insert a GO statement after your SET IDENTITY ... ON statement.

Answer 2

In a SQL query analyser session I would issue a GO after the IDENTITY_INSERT and before the actual insert. Maybe this applies to calling it from in code also?

Answer 3

It would be worth looking at SqlBulkCopy as it is optimized at importing bulk data.

Also worth looking into depending on the scenario would be to use an SSIS task to bulk copy the data (also uses the bulk copy API) or linked servers with a BULK INSERT statement (again, bulk copy API).

As for setting the identity are the set identity_insert statements executed on the same connection as the insert? As SQL Server 2005 only persists that setting on the current connection.

As the following worked fine for me on SQL Server 2008 Express:

using (var conn = CreateConnection())
{
    conn.Open();
    new SqlCommand("SET IDENTITY_INSERT Foo ON", conn).ExecuteNonQuery();
    new SqlCommand("INSERT INTO Foo(id) VALUES (1)", conn).ExecuteNonQuery();
    new SqlCommand("SET IDENTITY_INSERT Foo OFF", conn).ExecuteNonQuery();
}