查第N个1000 exec('select top 1000 * from TableName id not in (select top '+cast(1000*@N as varchar(10))+' from TableName')
CREATE PROC TEST @A INT @B INT AS DECLARE MYCUR SCROLL CURSOR FOR select * from yourtablename while @a <@b begin open mycur fetch absolute @a from mycursor set @a=@a+1 end close mycur deallocate mycur
下面的语句比not in语句效率要高很多!头1000条: select top 1000 * from yourTable where 主键字段 is not null;第1001条到第2000条: select top 1000 a.* from yourTable a left outer join (select top 1000 * from yourTable) b on a.主键字段 = b.主键字段 where b.主键字段 is null;第2001条到第3000条: select top 1000 a.* from yourTable a left outer join (select top 2000 * from yourTable) b on a.主键字段 = b.主键字段 where b.主键字段 is null;以此类推 ……
查第N个1000 exec('select top 1000 * from TableName id not in (select top '+cast(1000*(@N-1) as varchar(10))+' from TableName')
调试过的程序 CREATE PROC TEST1 @A INT , @B INT AS DECLARE MYCUR SCROLL CURSOR FOR select * from YOURNAME while @a <@b begin DECLARE @CON1 INT, DECLARE @CON2 VARCHAR fetch absolute @a from mycur INTO @CON1,@CON2 INSERT INTO WANG SELECT @CON1,@CON2 set @a=@a+1 end close mycur deallocate mycur
再和你的主键结合起来用就行了
exec('select top 1000 * from TableName id not in
(select top '+cast(1000*@N as varchar(10))+' from TableName')
@A INT
@B INT
AS
DECLARE MYCUR SCROLL CURSOR FOR select * from yourtablename
while @a <@b
begin
open mycur
fetch absolute @a from mycursor
set @a=@a+1
end
close mycur
deallocate mycur
select top 1000 * from yourTable where 主键字段 is not null;第1001条到第2000条:
select top 1000 a.* from yourTable a
left outer join (select top 1000 * from yourTable) b
on a.主键字段 = b.主键字段
where b.主键字段 is null;第2001条到第3000条:
select top 1000 a.* from yourTable a
left outer join (select top 2000 * from yourTable) b
on a.主键字段 = b.主键字段
where b.主键字段 is null;以此类推
……
exec('select top 1000 * from TableName id not in
(select top '+cast(1000*(@N-1) as varchar(10))+' from TableName')
CREATE PROC TEST1
@A INT ,
@B INT
AS
DECLARE MYCUR SCROLL CURSOR FOR select * from YOURNAME
while @a <@b
begin
DECLARE @CON1 INT,
DECLARE @CON2 VARCHAR
fetch absolute @a from mycur INTO @CON1,@CON2
INSERT INTO WANG SELECT @CON1,@CON2
set @a=@a+1
end
close mycur
deallocate mycur