sqlserver2000数据库，如何在存储过程中将int类型id字段设置为自增？

如题，我create table的时候，id是不自增的，因为我写存储过程：先需要手动往数据库里导入一批数据，导完之后， 就需要在存储过程中设置id字段为自增，然后再insert into select 模式导入另外一批数据进去。

这其中，如何写sql语句让id字段从不自增变成自增呢？

【注释】：手工操作的，原始添加自增字段的sql语句大伙就不要回了，这些不是我想要的，我想要的是在存储过程里面如何把已经存在的id字段从不自增改为自增。

分不够，我明天再加100，大家一定要帮我把这个问题解决啊？谢谢了！

18 个解决方案

#1

--設為自增
SET IDENTITY_INSERT tb on
...
SET IDENTITY_INSERT tb off

#2

不能的,你在企业管理企里面可以修改字段为自增长,但是它实际上是重新建了一个表,然后把原表的数据导进来.

解决办法是创建表的时候就创建成自增长的,然后用 SET IDENTITY_INSERT tb off/ON 开关.

#3

引用 1 楼 wufeng4552 的回复:

SQL code--設為自增
SET IDENTITY_INSERT tb on
...
SET IDENTITY_INSERT tb off

问下tb是tablename吗？我在查询分析器里面执行了，不行的！报如下错误：

服务器: 消息 8106，级别 16，状态 1，行 1
表 'tt' 没有标识属性。无法执行 SET 操作。

#4

引用 3 楼 DiligencyMan 的回复:

引用 1 楼 wufeng4552 的回复:
SQL code--設為自增 
SET IDENTITY_INSERT tb on 
... 
SET IDENTITY_INSERT tb off 
 

问下tb是tablename吗？我在查询分析器里面执行了，不行的！报如下错误： 

SQL code服务器: 消息 8106，级别 16，状态 1，行 1
表 'tt' 没有标识属性。无法执行 SET 操作。

--把ID 設為自增列
if object_id('tb')is not null drop table tb
go
create table tb(ID int identity,[Name] varchar(10))
set identity_insert tb off
insert tb select 'A' union all select 'B'
go
set identity_insert tb on
go
insert tb(id,[name]) select 4,'C'
select * from tb
/*ID          Name       
----------- ---------- 
1           A
2           B
4           C

(影響 3 個資料列)
*/

#5

引用 2 楼 xieyueqing 的回复:

不能的,你在企业管理企里面可以修改字段为自增长,但是它实际上是重新建了一个表,然后把原表的数据导进来. 

解决办法是创建表的时候就创建成自增长的,然后用 SET IDENTITY_INSERT tb off/ON 开关.

这个方法也试过了，不好使！自增永远是自增，不管你在查询分析器里面执行SET IDENTITY_INSERT tb off/ON 也好，一点变化也没有的！
我的死后sql2000数据库。

#6

引用 4 楼 wufeng4552 的回复:

引用 3 楼 DiligencyMan 的回复:
引用 1 楼 wufeng4552 的回复: 
SQL code--設為自增 
SET IDENTITY_INSERT tb on 
... 
SET IDENTITY_INSERT tb off 


问下tb是tablename吗？我在查询分析器里面执行了，不行的！报如下错误： 

SQL code服务器: 消息 8106，级别 16，状态 1，行 1 
表 'tt' 没有标识属性。无法执行 SET 操作。 
 

SQL code--把ID 設為自增列
if object_id('tb')is not null drop table tb
go
…

我执行你的代码报如下错误：

if object_id('tb')is not null drop table tb
go
create table tb(ID int identity,[Name] varchar(10))
set identity_insert tb off
insert tb select 'A' union all select 'B'
go
set identity_insert tb on
go
insert tb(id,[name]) select 4,'C'
select * from tb

（所影响的行数为 2 行）

服务器: 消息 8107，级别 16，状态 1，行 1
表 'sqmis.dbo.tt' 的 IDENTITY_INSERT 已经为 ON。无法对表 'tb' 执行 SET 操作。
服务器: 消息 544，级别 16，状态 1，行 1
当 IDENTITY_INSERT 设置为 OFF 时，不能向表 'tb' 中的标识列插入显式值。

（所影响的行数为 2 行）

#7

执行完后，数据中的数据如下：

1	A
2	B

#8

这个需求做成你那样会报错吧。

一般是先设定为自增。

既然你需求要设为自增，那插入的时候你就不要插入这一列嘛。搞的那么麻烦

#9

引用 8 楼 flairsky 的回复:

这个需求做成你那样会报错吧。 

一般是先设定为自增。 

既然你需求要设为自增，那插入的时候你就不要插入这一列嘛。搞的那么麻烦

那个表明

set identity_insert tb off

这个语句执行不起作用！

#10

引用 4 楼 wufeng4552 的回复:

引用 3 楼 DiligencyMan 的回复:
引用 1 楼 wufeng4552 的回复: 
SQL code--設為自增 
SET IDENTITY_INSERT tb on 
... 
SET IDENTITY_INSERT tb off 
问下tb是tablename吗？我在查询分析器里面执行了，不行的！报如下错误：
SQL code服务器: 消息 8106，级别 16，状态 1，行 1 
表 'tt' 没有标识属性。无法执行 SET 操作。 
SQL code--把ID 設為自增列
if object_id('tb')is not null drop table tb
go
…

站在巨人的肩膀上，写完我的sql脚本代码：

if object_id('tb')is not null drop table tb
go
--创建自增表
create table tb(ID int identity,[Name] varchar(10))
go
--【注意】：修改为不自增的时候，企业管理器里面“设计表-->id标识”可能任然显示为‘是’，但是其实内部机制已经允许插入id了，
--所以执行下面插入时候操作的时候才会成功的。

--修改为不自增列
set identity_insert tb on
go
insert tb(id,[name]) select 4,'C'
select * from tb

--修改为自增列
go
set identity_insert tb off
insert tb select 'A' union all select 'B'
go

select * from tb

在我这里是跑通过了的！楼主多试下，多试下吧！

#11

引用 9 楼 DiligencyMan 的回复:

引用 8 楼 flairsky 的回复:
这个需求做成你那样会报错吧。 

一般是先设定为自增。 

既然你需求要设为自增，那插入的时候你就不要插入这一列嘛。搞的那么麻烦 
 

那个表明
SQL codeset identity_insert tb off

这个语句执行不起作用！ 

一个库中，只能有一个表的identity_insert设置为ON，不能把tt和tb的identity_insert都设为ON，先关掉原来的，再开新的

#12

在同一个会话中，只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。

你可以在两个查询窗口中分别设置，就互不影响了。

#13

由于手工修改对应的sql代码比较复杂，得导入导出，还得更名，有点不划算。
可以参考下面的方案：

create table ta(id int,c int)
insert ta select 1,2 union all select 8,100
--创建一个与ta结构完全相同的空临时表，id具备自增属性
select id=identity(int,1,1),c into #ta from ta where 1=2
--将数据插入临时表
insert #ta
select 4 union all select 6 union all select 20
--将数据插回原表
declare @maxid int
select @maxid=max(id) from ta

insert ta
select id+@maxid,c from #ta
--查看结果
select * from ta
/*
id          c
----------- -----------
1           2
8           100
9           4
10          6
11          20

(5 行受影响)
*/
drop table #ta
drop table ta
drop table tb

#14

--这样写保险一些
select @maxid=isnull(max(id),0) from ta

#15

if object_id('tb')is not null drop table tb
go
create table tb(ID int identity,[Name] varchar(10))
set identity_insert tb off
insert tb select 'A' union all select 'B'
go
set identity_insert tb on
go
insert tb(id,[name]) select 4,'C'
select * from tb

（所影响的行数为 2 行）

服务器: 消息 8107，级别 16，状态 1，行 1
表 'sqmis.dbo.tt' 的 IDENTITY_INSERT 已经为 ON。无法对表 'tb' 执行 SET 操作。
服务器: 消息 544，级别 16，状态 1，行 1
当 IDENTITY_INSERT 设置为 OFF 时，不能向表 'tb' 中的标识列插入显式值。

（所影响的行数为 2 行）

如12楼所言,你先set identity_insert tt off
再执行这个语句.

#16

SET IDENTITY_INSERT tb on 
... 
SET IDENTITY_INSERT tb off 

-----------------------------------------

set identity_insert tb off
insert tb select 'A' union all select 'B'
go
set identity_insert tb on

？？？

#17

世上本无事.

#18

我从ACCESS里面导入数据也遇到这个问题，很烦