declare @oldTB table ( col int ); insert into @oldTB values(1) insert into @oldTB values(2) insert into @oldTB values(3) insert into @oldTB values(5) Select ROW_NUMBER() over(order by col) as New_ID from @oldTBSQL2005里面可以使用Row_Number()函数对自增列编号
利用触发器实现标识列连续。(支持批量插入) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ttt] GO/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 ******/ CREATE TABLE [dbo].[ttt] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [time] [datetime] NULL ) ON [PRIMARY] GOALTER TABLE [dbo].[ttt] ADD CONSTRAINT [PK_ttt] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] GOinsert into ttt(name,time) values('logan',getdate()); insert into ttt(name,time) values('peter',getdate()); insert into ttt(name,time) values('man',getdate()); insert into ttt(name,time) values('lida',getdate()); insert into ttt(name,time) values('fcuandy',getdate());select * from ttt /* 1 logan 2008-12-15 17:36:37.780 2 peter 2008-12-15 17:36:37.780 3 man 2008-12-15 17:36:37.780 4 lida 2008-12-15 17:36:37.780 5 fcuandy 2008-12-15 17:36:37.793 */ GO CREATE TRIGGER tr ON ttt INSTEAD OF INSERT AS SET IDENTITY_INSERT ttt ON DECLARE @n INT SELECT @n=MAX(id) FROM ttt ;WITH fc AS ( SELECT n=1 UNION ALL SELECT nn=n+1 FROM fc WHERE n<@n ),fc1 AS ( SELECT n FROM fc a LEFT JOIN ttt b ON a.n = b.id WHERE b.id IS NULL ) INSERT ttt(id,name,time) SELECT n,name,time FROM (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a INNER JOIN ( SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted ) b ON a.idx=b.idx DECLARE @r INT SELECT @r=@@ROWCOUNT
SET IDENTITY_INSERT ttt OFF INSERT ttt(name,time) SELECT name,time FROM ( SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted ) x WHERE idx>@r GO DELETE FROM ttt WHERE name = 'peter' OR name='lida' GO INSERT ttt SELECT 'xxx',getdate() INSERT ttt SELECT 'yyy',GETDATE() GO SELECT * FROM ttt /* 1 logan 2008-12-15 17:37:20.967 2 xxx 2008-12-15 17:37:21.013 3 man 2008-12-15 17:37:20.967 4 yyy 2008-12-15 17:37:21.030 5 fcuandy 2008-12-15 17:37:20.967 */DELETE FROM ttt WHERE name ='xxx' OR name='yyy'INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()SELECT * FROM ttt/* 1 logan 2008-12-15 17:38:29.450 2 roy_88 2008-12-15 17:38:29.530 3 man 2008-12-15 17:38:29.467 4 limpire 2008-12-15 17:38:29.530 5 fcuandy 2008-12-15 17:38:29.467 6 熊 2008-12-15 17:38:29.530 */ 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx
if object_id('tb','U') is not null drop table tb go create table tb ( id int ) insert into tb select 1 union all select 2 union all select 3 union all select 5 union all select 6 go with t(row) AS ( select row=row_number() over(order by id) from tb ) select row from t where row not in(select id from tb) /* row -------------------- 4(1 行受影响)*/
declare @oldTB table
(
col int
);
insert into @oldTB values(1)
insert into @oldTB values(2)
insert into @oldTB values(3)
insert into @oldTB values(5)
Select ROW_NUMBER() over(order by col) as New_ID
from @oldTBSQL2005里面可以使用Row_Number()函数对自增列编号
执行语句 DBCC CHECKIDENT ('dbo.Person', RESEED, 10) 后再通过Management Studio添加数据时,ID就会自动从11开始,也就是new_reseed_value+1开始
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 ******/
CREATE TABLE [dbo].[ttt] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[time] [datetime] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[ttt] ADD
CONSTRAINT [PK_ttt] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOinsert into ttt(name,time) values('logan',getdate());
insert into ttt(name,time) values('peter',getdate());
insert into ttt(name,time) values('man',getdate());
insert into ttt(name,time) values('lida',getdate());
insert into ttt(name,time) values('fcuandy',getdate());select * from ttt
/*
1 logan 2008-12-15 17:36:37.780
2 peter 2008-12-15 17:36:37.780
3 man 2008-12-15 17:36:37.780
4 lida 2008-12-15 17:36:37.780
5 fcuandy 2008-12-15 17:36:37.793
*/
GO
CREATE TRIGGER tr ON ttt
INSTEAD OF INSERT
AS
SET IDENTITY_INSERT ttt ON
DECLARE @n INT
SELECT @n=MAX(id) FROM ttt
;WITH fc AS
(
SELECT n=1
UNION ALL
SELECT nn=n+1 FROM fc WHERE n<@n
),fc1 AS
(
SELECT n FROM fc a
LEFT JOIN ttt b
ON a.n = b.id
WHERE b.id IS NULL
)
INSERT ttt(id,name,time) SELECT n,name,time
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a
INNER JOIN
(
SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
) b
ON a.idx=b.idx
DECLARE @r INT
SELECT @r=@@ROWCOUNT
SET IDENTITY_INSERT ttt OFF
INSERT ttt(name,time) SELECT name,time FROM
(
SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
) x
WHERE idx>@r
GO
DELETE FROM ttt WHERE name = 'peter' OR name='lida'
GO
INSERT ttt SELECT 'xxx',getdate()
INSERT ttt SELECT 'yyy',GETDATE()
GO
SELECT * FROM ttt
/*
1 logan 2008-12-15 17:37:20.967
2 xxx 2008-12-15 17:37:21.013
3 man 2008-12-15 17:37:20.967
4 yyy 2008-12-15 17:37:21.030
5 fcuandy 2008-12-15 17:37:20.967
*/DELETE FROM ttt WHERE name ='xxx' OR name='yyy'INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()SELECT * FROM ttt/*
1 logan 2008-12-15 17:38:29.450
2 roy_88 2008-12-15 17:38:29.530
3 man 2008-12-15 17:38:29.467
4 limpire 2008-12-15 17:38:29.530
5 fcuandy 2008-12-15 17:38:29.467
6 熊 2008-12-15 17:38:29.530
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int
)
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6
go
with t(row) AS
(
select row=row_number() over(order by id) from tb
)
select row from t where row not in(select id from tb)
/*
row
--------------------
4(1 行受影响)*/
参照
http://blog.csdn.net/roy_88/article/details/1424370