set identity_insert tb1 on insert into tb1(id,c1,c2,..) select id,c1,c2,.. from tb2 set identity_insert tb1 off
--创建测试表 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 --*/
没有语句能够使已经存在的列变成自增的但是,方法还是有的。 --1 select * into tb_name_temp from tb_name where 1=0 --2 alter table tb_name_temp add id_temp int IDENTITY(1,1) --3 插入数据 set identity_insert tb_name_temp on insert into tb_name_temp(add id_temp, col1,col2.....) select id,col1,col2..... from tb_name set identity_insert tb_name_temp on其它的表也这么处理
http://www.cnblogs.com/qiantuwuliang/archive/2009/12/03/1616288.html
exec sp_rename '表名.[原列名]', '新列名'
我只是想把一个表的数据复制到另一个表,而且要连id也复制过去,然后把目标表的id在改成标识,以便以后添加数据id自动增长
set identity_insert tb1 on
insert into tb1(id,c1,c2,..)
select id,c1,c2,.. from tb2
set identity_insert tb1 off
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
--*/
--1
select * into tb_name_temp from tb_name where 1=0
--2
alter table tb_name_temp add id_temp int IDENTITY(1,1)
--3 插入数据
set identity_insert tb_name_temp on
insert into tb_name_temp(add id_temp, col1,col2.....)
select id,col1,col2..... from tb_name
set identity_insert tb_name_temp on其它的表也这么处理