你这个貌似只能使用临时表了.select a = identity(int,1,1) , b , c into tmp from a delete from tb insert into a select * from tmp drop table tmp
你这个貌似只能使用临时表了. select a = identity(int,1,1) , b , c into tmp from a delete from tb insert into tb select * from tmp drop table tmp
/*====================================================*/ -- Author: Ken Wong -- Create date: 2009-12-22 23:40:58 -- Description: /*====================================================*/ --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([A] int,[B] varchar(3),[C] varchar(6)) insert [tb] select 0,'121','dff' union all select 0,'12','sdf' union all select 0,'121','sdf' union all select 0,'12','sdfsdf' union all select 0,'121','sd' union all select 0,'12','fsd' union all select 0,'sd',null union all select 0,'12','sdf' union all select 0,'sf',null union all select 0,'21','df' union all select 0,'1','sdfsdf' union all select 0,'1','sdf' union all select 0,'1','sdf' union all select 0,'2','sfs' union all select 0,'1','sd' union all select 0,'21','dfs' union all select 0,'1','sdf'declare @i int select @i = 0 update [tb] set [A] = @i, @i=@i+1select * from [tb] ------------------ 1 121 dff 2 12 sdf 3 121 sdf 4 12 sdfsdf 5 121 sd 6 12 fsd 7 sd NULL 8 12 sdf 9 sf NULL 10 21 df 11 1 sdfsdf 12 1 sdf 13 1 sdf 14 2 sfs 15 1 sd 16 21 dfs 17 1 sdf
两种办法 一个是循环更新 一个是变成自增列 --创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO--1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GOUPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO--2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GOEXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO--显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/
delete from tb
insert into a select * from tmp
drop table tmp
delete from tb
insert into tb select * from tmp
drop table tmp
-- Author: Ken Wong
-- Create date: 2009-12-22 23:40:58
-- Description:
/*====================================================*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([A] int,[B] varchar(3),[C] varchar(6))
insert [tb]
select 0,'121','dff' union all
select 0,'12','sdf' union all
select 0,'121','sdf' union all
select 0,'12','sdfsdf' union all
select 0,'121','sd' union all
select 0,'12','fsd' union all
select 0,'sd',null union all
select 0,'12','sdf' union all
select 0,'sf',null union all
select 0,'21','df' union all
select 0,'1','sdfsdf' union all
select 0,'1','sdf' union all
select 0,'1','sdf' union all
select 0,'2','sfs' union all
select 0,'1','sd' union all
select 0,'21','dfs' union all
select 0,'1','sdf'declare @i int
select @i = 0
update [tb]
set [A] = @i,
@i=@i+1select * from [tb]
------------------
1 121 dff
2 12 sdf
3 121 sdf
4 12 sdfsdf
5 121 sd
6 12 fsd
7 sd NULL
8 12 sdf
9 sf NULL
10 21 df
11 1 sdfsdf
12 1 sdf
13 1 sdf
14 2 sfs
15 1 sd
16 21 dfs
17 1 sdf
一个是循环更新
一个是变成自增列
--创建测试表
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/