Разница в поведении SET IDENTITY_INSERT от SQL Server 2000 до 2005?

У меня есть приложение, которое (в настоящее время) должно использовать БД, обслуживаемые как SQL Server 2000, так и SQL Server 2005.

Я пытаюсь программно переместить БД с одного сервера на другой. Все таблицы, которые я использую, имеют автоинкрементный первичный ключ. Поскольку эти автоматически увеличивающиеся ключи используются в качестве внешних ключей в других таблицах, мой план атаки для копирования таблиц состоял в том, чтобы временно отключить автоинкремент в целевой таблице, ВСТАВИТЬ записи дословно (с исходными первичными ключами), а затем включить автоматический -инкремент обратно на.

Если я правильно понимаю, я могу "включать / выключать автоинкремент" по одной таблице за раз, выполнив

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

Моя реализация находится в VB 2005 с использованием System.Data.SqlClient.

Для краткости я покажу только строки кода, относящиеся к построению SQL-запросов.

Чтобы создать таблицу:

    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()

Чтобы включить или выключить IDENTITY_INSERT:

    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()

Чтобы вставить новую запись в обычном режиме без указания первичного ключа:

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

Чтобы вставить новую запись с явным первичным ключом:

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

Итак, мой план состоит в том, чтобы вызвать код, который включает IDENTITY_ INSERT, добавить записи с явными первичными ключами, а затем выключить IDENTITY_ INSERT.

Этот метод работает нормально, когда я разговариваю с SQL Server 2000, но я сталкиваюсь с исключением, когда пробую тот же код для SQL Server 2005:

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

Какие-нибудь намеки? Заранее спасибо, как всегда.


auto-increment sql-server-2005 sql-server-2000
person John    schedule 08.09.2009    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Стоит взглянуть на SqlBulkCopy, поскольку он оптимизирован для массового импорта. данные.

Также стоит изучить, в зависимости от сценария, будет использование задачи SSIS для массового копирования данных (также использует API массового копирования) или связанных серверов с инструкция BULK INSERT (опять же, API массового копирования).

Что касается установки идентификатора, выполняются ли операторы set identity_insert в том же соединении, что и вставка? Поскольку SQL Server 2005 сохраняет этот параметр только в текущем соединении.

Поскольку на 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();
}
person Chris Chilvers    schedule 08.09.2009
comment
Ты нашел это. Я открывал / закрывал соединение между командами. Также спасибо, что указали мне на SqlBulkCopy! - person John; 09.09.2009
comment
Ах ... неужели это случайно не связано с неявными транзакциями? - person David Andres; 09.09.2009
comment
Дэвид, возможно. Однако я недостаточно знаком, чтобы сказать наверняка. - person John; 09.09.2009
comment
SET IDENTITY_INSERT не заботится о транзакциях, он не участвует в них. Однако он заботится о соединениях, он устанавливается только для текущего соединения. Я не тестировал прицел, поэтому я не уверен в этом. - person Chris Chilvers; 09.09.2009

arrow_upward
1
arrow_downward

Возможно, вам придется вставить оператор GO после оператора SET IDENTITY ... ON.

person David Andres    schedule 08.09.2009
comment
Спасибо, что нашли время ответить, Дэвид! - person John; 09.09.2009

arrow_upward
1
arrow_downward

В сеансе анализатора запросов SQL я бы выдал GO после IDENTITY_INSERT и перед фактической вставкой. Может быть, это относится и к вызову из кода?

person Henryk    schedule 08.09.2009
comment
Спасибо что нашли время ответить! Очень признателен. - person John; 09.09.2009