Разница в поведении для 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."

Есть намеки? Заранее спасибо как всегда.

Источник

3 ответа

Решение

Стоит посмотреть на 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();
}
Источник

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

Источник

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

Источник
Другие вопросы по тегам