34,594
社区成员
发帖
与我相关
我的任务
分享
/*
id name
----------- ----------
1 张三
2 李四
3 王五
(3 行受影响)
*/
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int,
name varchar(10)
)
go
insert into tb select (select isnull(max(id)+1,'1') from tb),'张三'
insert into tb select (select isnull(max(id)+1,'1') from tb),'李四'
insert into tb select (select isnull(max(id)+1,'1') from tb),'王五'
Create table t(ID int identity(1,1) primary key,Name nvarchar(50))
go
insert t select 'a'
insert t select 'b'
go
select * from t
/*
ID Name
1 a
2 b
*/
drop table t
--创建测试表
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,
name varchar(10)
);
create function [dbo].[F_getID]()
returns int
as
begin
declare @maxid int
select @maxid=isnull(max(id),0) + 1 from tb
return @maxid
end
insert into tb(id,name) values(dbo.[F_getID](),'a')
insert into tb(id,name) values(dbo.[F_getID](),'b')
insert into tb(id,name) values(dbo.[F_getID](),'c')
insert into tb(id,name) values(dbo.[F_getID](),'d')
select * from tb
id name
----------- ----------
1 a
2 b
3 c
4 d
(4 行受影响)
create table tb
(
id int identity(1,1)
)