代码之家 › 专栏 › 技术社区 › John

从SQL Server 2000到2005,SET IDENTITY_INSERT的行为有什么不同?

auto-increment sql-server-2000 sql-server-2005

0

John · 技术社区 · 17 年前

我试图以编程方式将数据库从一台服务器移动到另一台服务器。我使用的所有表都有一个自动递增的主键。由于这些自动递增的键在其他表中用作外键,因此我复制表的攻击计划是暂时关闭目标表上的自动递增,逐字插入记录(使用原始主键),然后重新启用自动递增。

如果我理解正确,我可以通过执行

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

为了简洁起见,我只展示与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()

    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_uuu插入的代码,添加带有显式主键的记录,然后关闭IDENTITY_uuu插入。

在与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 回复 | 直到 17 年前

1

2

Community Mohan Dere 9 年前

值得一看 SqlBulkCopy

根据场景的不同,还值得研究的是使用SSIS任务批量复制数据(也使用批量复制API)或使用 BULK INSERT 语句(同样是批量复制API)。

至于设置标识,请参见 set identity_insert 在与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();
}

2

1

David Andres 17 年前

您可能需要在输入后插入GO语句 SET IDENTITY ... ON 陈述

3

1

Henryk 17 年前