如何修改表字段的标识列

Question

比如说我的表table的字段[客户ID] [int] IDENTITY (10001, 1) NOT NULL ,如何写脚本修改成没有标识[客户ID] [int]  NOT NULL？处理好后然后又需要还原成[客户ID] [int] IDENTITY (10001, 1) NOT NULL,请大家帮忙解决，谢谢！

Answer 1

SET IDENTITY_INSERT dbo.Tool ONINSERT INTO ...SET IDENTITY_INSERT dbo.Tool OFF

Answer 2

----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO----查看标识列
select * from syscolumns where id = object_id('tablename') and colstat = 1
----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tablename') and colstat = 1
GO----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO

Answer 3

----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tablename') and colstat = 1
GO
请问如何恢复标识列标记，如客户ID的标识种子10001，谢谢！

Answer 4

其实我是有这样一个表Table 客户（客户id ,客户名称），客户id是标识列，标识种子10001，客户表里有这样的数据 10001    aaaaa
                     10002    bbbbb
                     10003    ccccc
现在我要把另一数据库相同的表 Table 客户（客户id ,客户名称），客户id是标识列，标识种子80001，的数据
                     80001    aaaaa1
                     80002    bbbbb1
                     80003    ccccc1
插入到上面的客户表里，插入的客户id就从80001开始，插入完成后上面的客户表客户id还是从10003自动增长到80000，不知我表达清楚没？谢谢各位帮忙了。
就是两个不同数据库的同一个表结构，然后需要把一个表里的数据放到另一个相同表里的一个特定空间，放到特定空间的表需要能经常更新。

Answer 5

----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tablename') and colstat = 1
GO--插入id=8001-8003的行
...----恢复标识列标记
update syscolumns set colstat = 1 where id = object_id('tablename') and name = '标识列名称'----重新设置标识的起始值
DBCC CHECKIDENT (表名称, RESEED, 10003)----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO

Answer 6

我的存储过程如下：
CREATE PROCEDURE [P_客户转存] AS
----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('T_客户') and colstat = 1
GO--插入id=8001-8003的行INSERT T_客户
 SELECT *
    FROM  T_khtempDELETE  T_khtempGO
----恢复标识列标记
update syscolumns set colstat = 1 where id = object_id('T_客户') and name = '客户ID'----重新设置标识的起始值
DBCC CHECKIDENT (T_客户, RESEED, 10001)----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO
但语法检查的时候提示如下错误：
错误259：未启用对系统目录的特殊更新，系统管理员必须重新配置sqlserver以允许这种操作。
请问该怎么处理？谢谢帮忙解答！

Answer 7

打开企业管理器,右键点击SQLSERVER实例名称,从右键菜单中选择"属性",在<服务器设置>选项卡的<服务器行为>中,复选"允许对系统目录进行直接修改".注意:之后记得该回来.

Answer 8

jinjazz(近身剪) SET IDENTITY_INSERT dbo.Tool ONINSERT INTO ...SET IDENTITY_INSERT dbo.Tool OFF
------------------------------------------------为什么不用这个，暂时禁用这个表的表示列，反而要去操作系统表
需要的权限就不说，而且还有风险，你能让你的用户用管理员权限登陆吗？完全不明白

Answer 9

1、把所有的go 去掉2、update 这样写declare @sql varchar(1000)set @sql=''set @sql='update syscolumns set colstat = 0 where id = object_id(''T_客户'') and colstat = 1'exec(@sql)

Answer 10

SET IDENTITY_INSERT 这样做是达不到lz的要求的 --测试
create table tab_1 (id int identity(1,1) ,nn varchar(100))insert into tab_1 select 'a'union all select 'b' SET IDENTITY_INSERT dbo.tab_1 ONINSERT INTO tab_1(id,nn) values(100,'c')SET IDENTITY_INSERT dbo.tab_1 OFFinsert into tab_1 select 'd'select * from tab_1

Answer 11

还是要加 DBCC CHECKIDENT (T_客户, RESEED, 10001)

Answer 12

请问如何写sql脚本对这样一个表Table 客户（客户id ,客户名称），客户id是标识列，标识种子10001，客户表里有这样的数据 10001    aaaaa
                              10002    bbbbb
                              10003    ccccc的客户ID进行更新，比如要让    10001    aaaaa
                              10002    bbbbb
                              10003    ccccc变成                          30001    aaaaa
                              30002    bbbbb
                              30003    ccccc
意思是我要更新
UPDATE  T_客户
SET 客户ID=客户ID+20000
怎么写语句先取消标识列，然后执行UPDATE  T_客户 SET 客户ID=客户ID+20000，最后恢复标识列，谢谢！

Answer 13

--测试
create table tab_1 (id int identity(10001,1) ,nn varchar(100))
insert into tab_1 select 'a'
union all select 'b' 
union all select 'c' 
----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tab_1') and colstat = 1
GO--更新原标识列
update tab_1 set id=id+20000----恢复标识列标记
update syscolumns set colstat = 1 where id = object_id('tab_1') and name = 'id'----重新设置标识的起始值
DBCC CHECKIDENT (tab_1, RESEED, 10001)----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO--查看效果
select * from tab_1--删除测试表
drop table tab_1

Answer 14

CREATE PROCEDURE [P_更新客户] 
ASSET IDENTITY_INSERT dbo.Tool ONINSERT INTO dbo.T_客户
   SELECT *
   FROM dbo.T_khtempSET IDENTITY_INSERT dbo.Tool OFF
GO
语法检查时出错，错误8101，仅当使用了列的列表，并且IDENTITY_INSERT 为 ON时，才能在表dbo.T_客户中为标识列指定显示值，请问该如何写？

Answer 15

既然要向dbo.T_客户表中插入数据,为什么要设置为dbo.Tool呢?
改为:
SET IDENTITY_INSERT T_客户 ONINSERT INTO T_客户
   SELECT *
   FROM dbo.T_khtempSET IDENTITY_INSERT T_客户 OFFDBCC CHECKIDENT (T_客户, RESEED, 新的标识列起始值)

Answer 16

我要做这样一件事情，就是我的数据库里有这样一个表Table1 客户（客户id ,客户名称），客户id是标识列，标识种子10001，客户表里有这样的数据 10001    aaaaa
                                                  10002    bbbbb
                                                  10003    ccccc
然后执行如下脚本：
exec sp_configure 'allow updates',1
reconfigure with override
update syscolumns set colstat = 0 where id = object_id('T_客户') and colstat = 1
update T_客户 set 客户id=客户id+20000
update syscolumns set colstat = 1 where id = object_id('T_客户') and name = '客户ID'
DBCC CHECKIDENT (T_客户, RESEED, 30001)
exec sp_configure 'allow updates',0
reconfigure with override
GO
这样使表Table2 客户（客户id ,客户名称）的标识列从30001开始，接下来我要做的事情就是我想从另外的表里Table2 客户（客户id ,客户名称）查询数据插入到Table2 ，两个表结构完全一样，这个表的标识列是从10001到29999的，所以就有这段脚本：
SET IDENTITY_INSERT T_客户 ONINSERT INTO T_客户
   SELECT *
   FROM dbo.T_khtempSET IDENTITY_INSERT T_客户 OFF
这段脚本就是想先禁用标识列，然后插入数据，但存储过程语法检查有错误，错误如下：错误8101，仅当使用了列的列表，并且IDENTITY_INSERT 为 ON时，才能在表dbo.T_客户中为标识列指定显示值，请问该怎么样插入数据？请大家帮忙，先谢谢了！

Answer 17

if object_id('tbTest') is not null
    drop table tbTest
GO
----创建测试数据
create table tbTest(客户id int identity(10001,1),客户名称 varchar(10))
insert tbTest(客户名称) 
select 'aaaaa' union all
select 'bbbbb' union all
select 'ccccc'
----查看现有数据
select * from tbTest
GO
----允许修改系统表
exec sp_configure 'allow updates',1
reconfigure with override
GO
----修改系统表,取消标识列
update syscolumns set colstat = 0 where id = object_id('tbTest') and colstat = 1
----更新表客户id
update tbTest set 客户id=客户id+20000
----修改系统表,恢复标识列
update syscolumns set colstat = 1 where id = object_id('tbTest') and name = '客户ID'
GO
----重新设置表的标识列起始值
DBCC CHECKIDENT (tbTest, RESEED, 30001)
GO
----禁止修改系统表
exec sp_configure 'allow updates',0
reconfigure with override
GO
----允许向标识列中显式插入新值
SET IDENTITY_INSERT tbTest ON
GO
----向标识列中显式插入新值(注意:必须在insert中指定列名称列表,不能使用
----insert tbTest select 客户id值,客户名称值 的形式)
insert tbTest(客户id,客户名称)
select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'
----查看
select * from tbTest
GO
----禁止向标识列显式插入新值
SET IDENTITY_INSERT tbTest OFF
GO
----插入测试数据,观察客户id的变化
insert tbTest
select 'g' union all
select 'h' union all
select 'i' 
----查看
select * from tbTest----清除测试环境
DROP TABLE tbTest/*结果
客户id        客户名称       
----------- ---------- 
30001       aaaaa
30002       bbbbb
30003       ccccc
10001       111111
10002       222222
29999       333333
30002       g
30003       h
30004       i
*/

Answer 18

hellowork(一两清风) 你好！首先感谢你认真帮忙分析解答，真的很感谢！
你刚才写的这段中：
----允许向标识列中显式插入新值
SET IDENTITY_INSERT tbTest ON
GO
----向标识列中显式插入新值(注意:必须在insert中指定列名称列表,不能使用
----insert tbTest select 客户id值,客户名称值 的形式)
insert tbTest(客户id,客户名称)
select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'
----查看
select * from tbTest
GO
----禁止向标识列显式插入新值
SET IDENTITY_INSERT tbTest OFF
GO
不符合我的要求，insert tbTest(客户id,客户名称)
select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'我是要insert into tbTest(客户id,客户名称)
   SELECT *
   FROM dbo.T_khtemp
因为T_khtemp表里有2000条记录，所以select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'估计不现实？

Answer 19

我上面的代码完全是为了测试,本意是让你熟悉一下实现的步骤,看看测试结果是否符合你的要求.
测试中使用了
insert tbTest(客户id,客户名称)
select ... union all
select ... union all
select ...
这种语法形式,其实你只是不太熟悉union的语法,你完全可以使用:
insert into tbTest(客户id,客户名称)
   SELECT *
   FROM dbo.T_khtemp
来代替,试试吧.

Answer 20

我之所以使用union,是因为我在测试的时候并没有创建与T_khtemp对应的测试表,只好使用union来直接向测试表插入常量了.
另外,
insert tbTest(客户id,客户名称)
select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'
等同于这样:
insert tbTest(客户id,客户名称) values(10001,'111111')
insert tbTest(客户id,客户名称) values(10002,'222222')
insert tbTest(客户id,客户名称) values(29999,'333333')

Answer 21

现在关键就在这段语句，我自己这样写：
SET IDENTITY_INSERT T_客户 ON
INSERT INTO T_客户
   SELECT *
   FROM dbo.T_khtemp
SET IDENTITY_INSERT T_客户 OFF
不对，我自己也测试过了像你这样写是对的
SET IDENTITY_INSERT T_客户 ON
INSERT INTO T_客户
insert dbo.T_khtemp(客户id,客户名称) values(10001,'111111')
SET IDENTITY_INSERT T_客户 OFF
但我现在需要的是把insert dbo.T_khtemp(客户id,客户名称) values(10001,'111111')这个语句写成查询形式的语句出来？我是需要从dbo.T_khtemp(客户id,客户名称)表里查询出来这些值，
                                                  10001    aaaaa
                                                  10002    bbbbb
                                                  10003    ccccc
然后插入到T_客户表里，该怎么写？即是把insert T_客户(客户id,客户名称) values(10001,'111111')这段插入的具体值换成从dbo.T_khtemp表里查询出来的形式。

Answer 22

注意我回复中的注释:
----向标识列中显式插入新值(注意:必须在insert中指定列名称列表,不能使用
----insert tbTest select 客户id值,客户名称值 的形式)
以及:
insert into tbTest(客户id,客户名称,......)   /*[必须]指定列名称列表*/
SELECT *  FROM dbo.T_khtemp   
而[不是]:
INSERT INTO T_客户   /*缺少列名称列表*/
   SELECT *
   FROM dbo.T_khtemp

Answer 23

if object_id('tbTest') is not null
    drop table tbTest
if object_id('tbT_khtemp') is not null
    drop table tbT_khtemp
GO
----创建测试数据
create table tbTest(客户id int identity(10001,1),客户名称 varchar(10))
insert tbTest(客户名称) 
select 'aaaaa' union all
select 'bbbbb' union all
select 'ccccc'
----查看现有数据
select * from tbTest
----创建要复制的数据源表
create table tbT_khtemp(客户id int,客户名称 varchar(10))
insert tbT_khtemp
select 10001,'111111' union all
select 10002,'222222' union all
select 29999,'333333'
GO
----允许修改系统表
exec sp_configure 'allow updates',1
reconfigure with override
GO
----修改系统表,取消标识列
update syscolumns set colstat = 0 where id = object_id('tbTest') and colstat = 1
----更新表客户id
update tbTest set 客户id=客户id+20000
----修改系统表,恢复标识列
update syscolumns set colstat = 1 where id = object_id('tbTest') and name = '客户ID'
GO
----重新设置表的标识列起始值
DBCC CHECKIDENT (tbTest, RESEED, 30001)
GO
----禁止修改系统表
exec sp_configure 'allow updates',0
reconfigure with override
GO
----允许向标识列中显式插入新值
SET IDENTITY_INSERT tbTest ON
GO
----向标识列中显式插入新值(注意:必须在insert中指定列名称列表,不能使用
----insert tbTest select 客户id值,客户名称值 的形式)insert tbTest(客户id,客户名称)/*注意此处:将tbT_khtemp表的内容复制到tbTest表中*/
select * from tbT_khtemp ----查看
select * from tbTest
GO
----禁止向标识列显式插入新值
SET IDENTITY_INSERT tbTest OFF
GO
----插入测试数据,观察客户id的变化
insert tbTest
select 'g' union all
select 'h' union all
select 'i' 
----查看
select * from tbTest----清除测试环境
DROP TABLE tbTest,tbT_khtemp/*结果
客户id        客户名称       
----------- ---------- 
30001       aaaaa
30002       bbbbb
30003       ccccc
10001       111111
10002       222222
29999       333333
30002       g
30003       h
30004       i
*/

Answer 24

hellowork(一两清风) 谢谢你的解答。我现在怎么执行这段程序也报错了，前两天还可以的。我是在查询分析器里执行的下面这段更新标识列的脚本：
----允许修改系统表
exec sp_configure 'allow updates',1
reconfigure with override
GO
----修改系统表,取消标识列
update syscolumns set colstat = 0 where id = object_id('T_客户') and colstat = 1
----更新表客户id
update T_客户 set 客户ID=客户ID+20000
----修改系统表,恢复标识列
update syscolumns set colstat = 1 where id = object_id('T_客户') and name = '客户ID'
GO
----重新设置表的标识列起始值
DBCC CHECKIDENT (T_客户, RESEED, 30001)
GO
----禁止修改系统表
exec sp_configure 'allow updates',0
reconfigure with override
GO提示错误如下：已将配置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。
服务器: 消息 8102，级别 16，状态 1，行 1
无法更新标识列 '客户ID'。
检查标识信息: 当前标识值 '30001'，当前列值 '30001'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
已将配置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

Answer 25

----修改系统表,取消标识列
update syscolumns set colstat = 0 where id = object_id('T_客户') and colstat = 1
GO         /*在此处加个GO*/
----更新表客户id
update T_客户 set 客户ID=客户ID+20000
GO         /*在此处加个GO*/
----修改系统表,恢复标识列
update syscolumns set colstat = 1 where id = object_id('T_客户') and name = '客户ID'
GO

Answer 26

你帮我完整写出来好吗？我现在这么写的
exec sp_configure 'allow updates',1
reconfigure with override
GO
----修改系统表,取消标识列
update syscolumns set colstat = 0 where id = object_id('T_客户') and colstat = 1
GO
----更新表客户id
update T_客户 set 客户ID=客户ID+20000
GO
----修改系统表,恢复标识列
update syscolumns set colstat = 1 where id = object_id('T_客户') and name = '客户ID'
GO
----重新设置表的标识列起始值
DBCC CHECKIDENT (T_客户, RESEED, 30001)
GO
----禁止修改系统表
exec sp_configure 'allow updates',0
reconfigure with override
GO
执行还是有错误。
错误如下：
已将配置选项 'allow updates' 从 1 改为 1。请运行 RECONFIGURE 语句以安装。（所影响的行数为 1 行）服务器: 消息 604，级别 21，状态 5，行 1
未能在 sysobjects 中找到数据库 'T_客户' 的对象 ID 1653737094 对应的行。请对 sysobjects 运行DBCC CHECKTABLE。
ODBC: 消息 0，级别 16，状态 1
通讯链接失败
ODBC: 消息 0，级别 16，状态 1
通讯链接失败
ODBC: 消息 0，级别 16，状态 1
通讯链接失败连接中断

Answer 27

请运行一下最近一次以
if object_id('tbTest') is not null
    drop table tbTest
if object_id('tbT_khtemp') is not null
    drop table tbT_khtemp
开头的回复中的代码测试一下,如果在你的机器上运行正常,就说明是你的'T_客户'表出现问题了,可能是客户ID不是identity列了,把客户id重新设为identity属性再试一下.

Answer 28

谢谢hellowork(一两清风) 的热情解答！很感谢！