更新时间:2023-02-04 22:57:45
总结
SQL Server 不允许您在标识列中插入显式值,除非您使用列列表.因此,您有以下选择:
SQL Server won't let you insert an explicit value in an identity column unless you use a column list. Thus, you have the following options:
或
tbl_A_archive
中的标识列成为常规的非标识 列:如果您的表是归档表并且您始终为标识列指定显式值,为什么你甚至需要一个身份列?只需使用常规 int 即可.tbl_A_archive
a regular, non-identity column: If your table is an archive table and you always specify an explicit value for the identity column, why do you even need an identity column? Just use a regular int instead.解决方案 1 的详细信息
代替
SET IDENTITY_INSERT archive_table ON;
INSERT INTO archive_table
SELECT *
FROM source_table;
SET IDENTITY_INSERT archive_table OFF;
你需要写
SET IDENTITY_INSERT archive_table ON;
INSERT INTO archive_table (field1, field2, ...)
SELECT field1, field2, ...
FROM source_table;
SET IDENTITY_INSERT archive_table OFF;
with field1, field2, ...
包含表中所有列的名称.如果您想自动生成该列列表,请查看 Dave's answer 或 安多玛的回答.
with field1, field2, ...
containing the names of all columns in your tables. If you want to auto-generate that list of columns, have a look at Dave's answer or Andomar's answer.
解决方案 2 的详细信息
不幸的是,不能只更改类型".从一个身份 int 列到一个非身份 int 列.基本上,您有以下选择:
Unfortunately, it is not possible to just "change the type" of an identity int column to a non-identity int column. Basically, you have the following options:
或
Identity Specification
/(Is Identity)
属性设置为 No
.在幕后,这将创建一个脚本来重新创建表并复制现有数据,因此,您还需要取消设置 Tools
/Options
/Designers
/Table and Database Designers
/防止保存需要重新创建表的更改
.Identity Specification
/(Is Identity)
property of the identity column in your archive table to No
. Behind the scenes, this will create a script to re-create the table and copy existing data, so, to do that, you will also need to unset Tools
/Options
/Designers
/Table and Database Designers
/Prevent saving changes that require table re-creation
.或