34,590
社区成员
发帖
与我相关
我的任务
分享
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a int, b int, c int)
go
insert into tb
select 1,2,3 union all
select 2,3,3
go
alter table tb add aaa int identity(1,1) primary key
select * from tb
/*------
a b c aaa
1 2 3 1
2 3 3 2
-----*/
insert tb values(4,2,3)
select * from tb
/*------------
a b c aaa
1 2 3 1
2 3 3 2
4 2 3 3
-------*/
alter table tb add aaa int identity(1,1)
select indentity(int,1,1) aaa, * into a from 表
drop table 表
sp_rename N'[dbo].[a]', N'表'
select indentity(int,1,1) 序, * into a from 表
drop table 表
sp_rename N'[dbo].[a]', N'表'
--创建测试表
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
--*/
ALTER TABLE TB ADD AAA INT PRIMARY KEY
ALTER TABLE TB ALTER AAA INT IENTITY(1,1)
??create table test(id int,code varchar(10))
insert into test
select 11,'aaaa'
union select 12,'aaab'
union select 13,'aaac'
union select 14,'aaad'
go
alter table test add aaa int identity(1,1)
go
select * from test
/*
id code aaa
----------- ---------- -----------
11 aaaa 1
12 aaab 2
13 aaac 3
14 aaad 4
*/
go
drop table test
go
alter table 表 add aaa int identity(1,1)