日期:2014-05-18 浏览次数:20527 次
利用触发器实现标识列连续。(支持批量插入) 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] GO ALTER TABLE [dbo].[ttt] ADD CONSTRAINT [PK_ttt] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] GO insert 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
------解决方案--------------------
--使用dbcc checkident检查和设置表的标识值 create table tb ( id int primary key identity, name varchar(50) ) insert into tb select 'a' union all select 'b' union all select 'c' union all select 'd' go dbcc checkident(tb,noreseed) go delete from tb where id>2 go --删除记录后,表tb只剩下两条记录了,但是此时表tb的标识值仍为4,可以用下面的语句重置标识值为2 dbcc checkident(tb,reseed,2) go dbcc checkident(tb,noreseed) go
------解决方案--------------------
取消自增长,手动填入ID
------解决方案--------------------
使用truncate即可,LZ试试吧。
------解决方案--------------------
楼主,提供下面两种方法,希望可以解决你的问题。
方法一: