22,209
社区成员
发帖
与我相关
我的任务
分享
create table ControlTable (ControlId int primary key, FunctionId int, DeviceOrderName varchar(256), DeviceOrder varchar(256), SelfDeviceOrder varchar(256))
insert into ControlTable (ControlId, FunctionId, DeviceOrderName, DeviceOrder, SelfDeviceOrder) values(:ControlId, :FunctionId, :DeviceOrderName, :DeviceOrder, :SelfDeviceOrder)
--创建测试表
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO
--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GO
UPDATE 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
GO
EXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO
--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/
create table #tb(id int, ident int identity(10000, 1))
insert into #tb
select 1000 union all
select 1001 union all
select 1002 union all
select 1003
alter table #tb
drop column ident
alter table #tb
add ident int identity(20000,1)
select * from #tb