我有一个100万条数据的库,想实现:查询前100条数据,并同时将这100条数据从库里删除。
这一过程中要防止这些数据被其他查询者获取,保证查询的数据唯一性。
在ASP里,我用游标方式查询后并循环删除可以实现,但是效率低下不说,还占用大量的服务器资源! 求教各位高手!给出例子,不知道用纯SQL或用储存过程是否可以实现。
这一过程中要防止这些数据被其他查询者获取,保证查询的数据唯一性。
在ASP里,我用游标方式查询后并循环删除可以实现,但是效率低下不说,还占用大量的服务器资源! 求教各位高手!给出例子,不知道用纯SQL或用储存过程是否可以实现。
delete a from(select top 100 * from 表) a
把前100条记录放到临时表后删除原表记录
SELECT TOP 100 * INTO #TEMP FROM TB
DELETE TOP (100) FROM TB
SELECT * FROM #TEMP
DROP TABLE #TEMP
insert into @t select top 100 id,name from sysobjectsdelete top(10) from @t output deleted.id, deleted.name
/*
4 sysrowsetcolumns
5 sysrowsets
7 sysallocunits
8 sysfiles1
13 syshobtcolumns
15 syshobts
25 sysftinds
26 sysserefs
27 sysowners
29 sysprivs
*/
http://blog.csdn.net/jinjazz/archive/2009/09/04/4520749.aspx
as
select top 100 * into #a from splsk
delete from splsk where plh in(select top 100 plh from splsk)
select * from #a
drop table #adeclare @sp nvarchar(100)
set @sp=N'splsk'
select * from @sp
goexec dbo.tba用存储过程吧!
as
begin tran
select top 100 * into #a from splsk(nolock)
delete from splsk with(xlock,paglock) where plh in(select top 100 plh from splsk(nolock))
select * from #a
drop table #acommit tran
go楼主可以模拟一下!在CMD中调用批处理
SQL codecreateproc dbo.tbaasselecttop100*into #afrom splskdeletefrom splskwhere plhin(selecttop100 plhfrom splsk)select*from #adroptable #adeclare@spnvarchar(100)set@sp=N'splsk'select*from@spgoexec db¡
[/Quote]
顶这个吧 不过有点==
create proc dbo.tba
as
begin tran
select top 100 * into #a from splsk(nolock)
delete from splsk with(xlock,paglock) where plh in(select top 100 plh from splsk(nolock))
select * from #a
drop table #acommit tran
go
此过程测试了一下,不能删除原库中的数据!不知何故
select top 100 * into #a from splsk(nolock)
--select @@rowcount查询一下所影响的行数是多少
delete from splsk with(xlock,paglock) where plh in(select top 100 plh from splsk(nolock))
--select @@rowcount查询一下所影响的行数是多少
select * from #a
drop table #a
select top 100 * into #a from splsk(nolock)
和
delete from splsk with(xlock,paglock) where plh in(select top 100 plh from splsk(nolock)) 都是成功影响100条数据而且这样单独调用,原数据也成功删除了100条!但是在调用储存过程时,却不起作用!
as
begin tran
select top 100 * into #a from splsk(nolock)
delete from splsk with(xlock,paglock) where plh in(select top 100 plh from splsk(nolock))
select * from #a
drop table #acommit tran
go
exec dbo.tbaa
/*(100 行受影响)(100 行受影响)(100 行受影响)
*/表名你改了没有splsk是我数据库中的表!
当然改了!
代码改后:create proc dbo.tba
asBEGIN TRAN
SELECT TOP 100 * into #a FROM table1(nolock)
DELETE FROM table1 WITH (xlock, paglock) WHERE id IN(SELECT TOP 100 id FROM table1(nolock))
select * from #a
drop table #aCOMMIT TRAN
GO