想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SETIDENTITY_INSERT 示例: 1.首先建" />
当前位置: 澳门新濠3559 > 数据库 > 正文

正确例子,1.首先建立一个有标识列的表

时间:2019-10-07 08:55来源:数据库
IDENTITY_INSERT 为 ON 时 , 必须把需要插入的列名列出来 不然报错 style="font-size: 16px;">想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SETIDENTITY_INSERT 示例: 1.首先建

IDENTITY_INSERT 为 ON 时 , 必须把需要插入的列名列出来 不然报错

style="font-size: 16px;">想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SET IDENTITY_INSERT

示例:

1.首先建立一个有标识列的表:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

2.尝试在表中做以下操作:
INSERT INTO products (id, product) VALUES(3, 'garden shovel')

结果会导致错误:“当 IDENTITY_INSERT 设置为 OFF 时,不能向表 'products' 中的标识列插入显式值。”

3.改用:
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')

返回正确。

style="font-size: 16px;">4.建立另外一个表products2,尝试相同插入操作:
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))

然后执行:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')

导致错误:“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。”

改为执行:
SET IDENTITY_INSERT products OFF
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')

执行通过。

5.尝试以下操作:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 SELECT * FROM products

导致错误:“仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'products2' 中为标识列指定显式值。”

6.改为:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) SELECT * FROM products

执行通过。

总结:

style="color: #ff0000; font-size: 16px;">1.每一次连接会话中的任一时刻,只能对一个表设置IDENTITY_INSERT ON,且设置只对当前会话有效;
2.在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。

附:
SQL Server帮助文档相关内容

SET IDENTITY_INSERT

允许将显式值插入表的标识列中。

语法

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

参数

database

是指定的表所驻留的数据库名称。

owner

是表所有者的名称。

table

是含有标识列的表名。

注释

任何时候,会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON,并且为另一个表发出了 SET IDENTITY_INSERT ON 语句,则 Microsoft® SQL Server™ 返回一个错误信息,指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。

如果插入值大于表的当前标识值,则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置,而不是在分析时设置。

权限

执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。

示例

下例创建一个含有标识列的表,并显示如何使用 SET IDENTITY_INSERT 设置填充由 DELETE 语句导致的标识值中的空隙。

-- Create products table. CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) GO -- Inserting values into products table. INSERT INTO products (product) VALUES ('screwdriver') INSERT INTO products (product) VALUES ('hammer') INSERT INTO products (product) VALUES ('saw') INSERT INTO products (product) VALUES ('shovel') GO -- Create a gap in the identity values. DELETE products WHERE product = 'saw' GO SELECT * FROM products GO -- Attempt to insert an explicit ID value of 3; -- should return a warning. INSERT INTO products (id, product) VALUES(3, 'garden shovel') GO -- SET IDENTITY_INSERT to ON. SET IDENTITY_INSERT products ON GO -- Attempt to insert an explicit ID value of 3 INSERT INTO products (id, product) VALUES(3, 'garden shovel'). GO SELECT * FROM products GO -- Drop products table. DROP TABLE products GO

想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SET IDENTITY_INSERT

It appears you have a table with an IDENTITY column and you're trying to INSERT an explicit value into that column. Usually when you have an IDENTITY column you don't insert that value and SQL Server supplies the next value in sequence. You can SELECT back that value by doing a SELECT SCOPE_IDENTITY() right after the INSERT.

正确例子:

示例:

If you truly do want to INSERT an explicit value into an IDENTITY column do it like this.

SET IDENTITY_INSERT  table(表名) ON
insert into table(id,name) value(1,名称)

1.首先建立一个有标识列的表:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

SET IDENTITY_INSERT tableName ON

SET IDENTITY_INSERT table OFF

2.尝试在表中做以下操作:
INSERT INTO products (id, product) VALUES(3, 'garden shovel')

... INSERT statement here ...

结果会导致错误:“当 IDENTITY_INSERT 设置为 OFF 时,不能向表 'products' 中的标识列插入显式值。”

SET IDENTITY_INSERT tableName OFF

3.改用:
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')

After you've INSERTed an explicit value into an identity column you'll probably need to correct the see as docemented here:

返回正确。

I usually only insert explicit values into an identity when I'm importing data. If you're doing during your regular processing I'd reconsider whether you really want an identity column.

4.建立另外一个表products2,尝试相同插入操作:
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))

Syntax is :

然后执行:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')

SET IDENTITY_INSERT [ database**. [ owner.* ] ] { table* } ON

导致错误:“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。”

改为执行:
SET IDENTITY_INSERT products OFF
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')

执行通过。

5.尝试以下操作:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 SELECT * FROM products

导致错误:“仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'products2' 中为标识列指定显式值。”

6.改为:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) SELECT * FROM products

执行通过。

总结:

1.每一次连接会话中的任一时刻,只能对一个表设置IDENTITY_INSERT ON,且设置只对当前会话有效;
2.在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。

附:
SQL Server帮助文档相关内容

SET IDENTITY_INSERT

允许将显式值插入表的标识列中。

语法

SET IDENTITY_INSERT [ database.[ owner.] ]澳门新濠3559, { table } { ON | OFF }

参数

database

是指定的表所驻留的数据库名称。

owner

是表所有者的名称。

table

是含有标识列的表名。

注释

任何时候,会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON,并且为另一个表发出了 SET IDENTITY_INSERT ON 语句,则 Microsoft® SQL Server™ 返回一个错误信息,指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。

如果插入值大于表的当前标识值,则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置,而不是在分析时设置。

权限

执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。

示例

下例创建一个含有标识列的表,并显示如何使用 SET IDENTITY_INSERT 设置填充由 DELETE 语句导致的标识值中的空隙。

-- Create products table. CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) GO -- Inserting values into products table. INSERT INTO products (product) VALUES ('screwdriver') INSERT INTO products (product) VALUES ('hammer') INSERT INTO products (product) VALUES ('saw') INSERT INTO products (product) VALUES ('shovel') GO -- Create a gap in the identity values. DELETE products WHERE product = 'saw' GO SELECT * FROM products GO -- Attempt to insert an explicit ID value of 3; -- should return a warning. INSERT INTO products (id, product) VALUES(3, 'garden shovel') GO -- SET IDENTITY_INSERT to ON. SET IDENTITY_INSERT products ON GO -- Attempt to insert an explicit ID value of 3 INSERT INTO products (id, product) VALUES(3, 'garden shovel'). GO SELECT * FROM products GO -- Drop products table. DROP TABLE products GO

 

转自:

编辑:数据库 本文来源:正确例子,1.首先建立一个有标识列的表

关键词: 澳门新濠3559