`

SET IDENTITY_INSERT 学习心得

 
阅读更多
http://www.cnblogs.com/kingjiong/archive/2010/02/08/1665949.html


想要将值插入到自动编号(或者说是标识列,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')

返回正确。

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

执行通过。

总结:

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
分享到:
评论

相关推荐

    SQL Server 之 SET IDENTITY_INSERT

    ### SQL Server 中 SET IDENTITY_INSERT 的使用方法及注意事项 #### 标题与描述解析 在SQL Server中,`SET IDENTITY_INSERT`是一个重要的命令,它主要用于处理标识列(即自动编号列,通常由`IDENTITY`关键字定义)...

    Set IDENTITY _INSERTY用法

    ### Set IDENTITY_INSERT 用法详解 在数据库管理与开发过程中,`SET IDENTITY_INSERT` 是一个非常实用的功能,主要用于允许或禁止手动插入带有 `IDENTITY` 属性的字段值。这一特性对于需要进行数据迁移或者特殊场景...

    sql Set IDENTITY_INSERT的用法

    然而,当我们需要插入特定的值时,例如在数据迁移或测试环境中,就需要使用 `SET IDENTITY_INSERT` 来开启这个功能。 `SET IDENTITY_INSERT` 的语法如下: ```sql SET IDENTITY_INSERT [ database.[ owner.] ] { ...

    sqlsever为标识列指定显式值

    解决这个问题的方法是,首先使用`SET IDENTITY_INSERT`命令将特定表的标识插入设置为ON,然后在插入语句中明确指定列列表,最后再关闭`IDENTITY_INSERT`。以下是一个示例: ```sql -- 开启IDENTITY_INSERT SET ...

    sqlserver自动增长列引起的问题解决方法

    3. 完成插入操作后,使用`SET IDENTITY_INSERT table_name OFF`关闭显式插入权限,恢复到默认状态,防止后续操作错误地修改自动增长列的值。 这个解决方案适用于在数据迁移、数据同步或复制场景下,但需要注意的是...

    DC工具中set_dont_touch和set_size_only的区别.doc

    `set_dont_touch` 和 `set_size_only` 是 DC 中的两个关键命令,它们提供了这样的保护机制,但它们的作用方式有所不同。 首先,`set_dont_touch` 命令用于标记设计中的某些元素(如 cell、net、reference 或 design...

    identity用法总结

    语法为:SET IDENTITY_INSERT [database.[ owner.]] {table } { ON |OFF },其中 database 是指定的表所驻留的数据库名称,owner 是表所有者的名称,table 是含有标识列的表名。 3、DBCC CHECKIDENT DBCC ...

    Insert语句向标识列中插入数据.pdf

    Set Identity_Insert identitytable on ``` 然后执行插入操作: ```sql Insert into identitytable (number, Name) values(5, '商场') ``` 这里我们指定了`number`列的值为5,`Name`列为'商场'。注意,这种方法需要...

    Mysql中LAST_INSERT_ID()的函数使用详解

    SET @last_student_id = LAST_INSERT_ID(); ``` 在这段代码中,`@last_student_id`变量将保存新插入的学生记录的ID。这种方式在需要将新生成的ID用于其他操作,比如在关联表中创建链接时非常有用。 在PHP中,你...

    SQL_SET.rar_SetSQL_sql_sql set _sql set connectattr_sql set rouc

    这些文档可能详细解释了如何使用上述的"SET"命令和其他数据库操作技巧,对于学习和查询SQL操作非常有帮助。 在实际工作中,理解和熟练使用这些"SET"命令能够极大地提高数据库管理效率,确保数据处理的准确性和性能...

    SQL Server 打开或关闭自增长

    这时,就可以利用`SET IDENTITY_INSERT`语句来打开或关闭自增长功能。 `SET IDENTITY_INSERT` 是SQL Server提供的一种特殊语法,它允许用户在具有自增长属性的列中显式插入值。这个功能非常有用,特别是在数据迁移...

    set_irq_affinity

    /sbin/set_irq_affinity eth1 可以进行中断绑定指定的cpu,提高网卡收包效率 把下面“eth1” 修改成对应的网卡名称 irq=$(cat /proc/interrupts | grep eth1 | cut -d':' -f 1); echo $irq for i in $irq ; do sudo...

    如何在数据库标识列里插入特定的值

    SET IDENTITY_INSERT TestIdentityGaps ON; INSERT INTO TestIdentityGaps (ID, Description) VALUES(2, 'Two Point One'); INSERT INTO TestIdentityGaps (ID, Description) VALUES(5, 'Five Point One'); SET ...

    在SQL Server数据库中为标识(IDENTITY)列插入显式值

    这时,我们可以使用`SET IDENTITY_INSERT`语句来开启对标识列显式插入值的功能。具体操作如下: 首先,我们需要使用`SET IDENTITY_INSERT [TableName] ON`命令来允许对特定表(如`member`)的标识列进行显式插入。...

    sql自动增长标识导致导入数据问题的解决方法

    使用方法是先用`SET IDENTITY_INSERT table_name ON`关闭自动增长,然后执行插入操作,最后用`SET IDENTITY_INSERT table_name OFF`恢复自动增长。需要注意的是,同一时间只能对一个表启用`IDENTITY_INSERT`,并且...

    计算机等考三级数据库知识辅导:自动排除计算字段拷贝表数据.docx

    这通过`set identity_insert @tableName on`语句实现。 3. **构建插入语句**:存储过程接着构建了一个SQL插入语句,用于拷贝数据。它遍历`syscolumns`视图,筛选出非计算字段(`iscomputed <> 1`),并将这些字段名...

    sql妙用(绝妙的sql语句)

    当你需要手动插入值或者重置IDENTITY列的种子值时,可以使用`SET IDENTITY_INSERT`来开启或关闭手动插入,以及`DBCC CHECKIDENT`来重置种子值。例如: ```sql SET IDENTITY_INSERT TableName ON INSERT INTO ...

Global site tag (gtag.js) - Google Analytics