create proc sp_insert as declare @i int select @i=isnull(max(id),0) from test begin tran select top 500 id=identity(int) into # from syscolumns insert test select id+@i from # drop table # commit go
create proc sp_insert as declare @i int select @i=isnull(max(id),0) from test begin tran IF @i>=10000 ROLLBACK ELSE BEGIN select top 500 id=identity(int) into # from syscolumns insert test select id+@i from # drop table # END commit GOcreate table test(id int not null) EXEC sp_insertSELECT * from test /* ..... (10000 行受影响) */
10分钟不可能的。执行一下两秒不到。这样试试: ALTER proc sp_insert AS SET NOCOUNT ON declare @i int select @i=isnull(max(id),0)+1 from test IF @i<=10000 BEGIN WHILE @i<=10000 BEGIN BEGIN TRAN INSERT test VALUES(@i) IF @@ERROR<>0 BEGIN DECLARE @s VARCHAR(10) SET @s=LTRIM(@i)+'插入出错!' RAISERROR(@s,16,1) END IF @i%500=0 COMMIT SET @i=@i+1 END END ELSE RAISERROR('当前数据已有10000行',16,1) SET NOCOUNT OFF GO IF OBJECT_ID('test') IS NOT NULL DROP TABLE test go create table test(id int not null)EXEC sp_insert
as
declare @i int
select @i=isnull(max(id),0) from test
begin tran
select top 500 id=identity(int) into # from syscolumns
insert test select id+@i from #
drop table #
commit
go
as
declare @i int
select @i=isnull(max(id),0) from test
begin tran
IF @i>=10000
ROLLBACK
ELSE
BEGIN
select top 500 id=identity(int) into # from syscolumns
insert test select id+@i from #
drop table #
END
commit
GOcreate table test(id int not null)
EXEC sp_insertSELECT * from test
/*
.....
(10000 行受影响)
*/
到每五百条的时候commit一次,此时判断是否commit成功,如果有异常则打印异常信息。
ALTER proc sp_insert
AS
SET NOCOUNT ON
declare @i int
select @i=isnull(max(id),0)+1 from test
IF @i<=10000
BEGIN
WHILE @i<=10000
BEGIN
BEGIN TRAN
INSERT test VALUES(@i)
IF @@ERROR<>0
BEGIN
DECLARE @s VARCHAR(10)
SET @s=LTRIM(@i)+'插入出错!'
RAISERROR(@s,16,1)
END
IF @i%500=0
COMMIT
SET @i=@i+1
END
END
ELSE
RAISERROR('当前数据已有10000行',16,1)
SET NOCOUNT OFF
GO
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test
go
create table test(id int not null)EXEC sp_insert