一个简单的游标删除SQL SERVER表，游标sqlserver表
use databaseName
declare @tblname char(100)
declare @sql char(5000)
declare table_cursor cursor for select name from sysobjects where name like 'tb_card[_]%' and name<>'Tb_card_regist' and name<>'Tb_card_item' and name<>'Tb_card_discrule' and name<>'tb_card_packagesetdetail' and name<>'tb_card_packagedetail' and name<>'tb_card_exchangeitem'
and name<>'tb_card_package'
open table_cursor
fetch next from table_cursor into @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='delete from
'+@tblname
exec(@sql)
print 'delete ' +@tblname + 'successful'
fetch next from table_cursor into @tblname
END
close table_cursor
deallocate table_cursor
declare @sex int
declare @grade int
declare my_youbiao cursor
for select sex ,grade from StudentTable
open my_youbiao
fetch next from my_youbiao into @sex, @grade
while @@fetch_status=0
begin
if @sex='男'
begin
update StudentTable set grade=@grade-'2' where current of my_youbiao
end
else
begin
update StudentTable set grade=@grade-'1' where current of my_youbiao
end
fetch next from my_youbiao into @sex, @grade
end
close my_youbiao
deallocate my_youbiao
/*******
假设你要处理的表名是: pludetail
可以用以下过程来实现,速度不在下面过程的考虑之中
*********/
create procedure distinct_deal
as
begin
begin transaction
select distinct * into #tempdel from pludetail --提取无重复的记录到临时表中
truncate table pludetail --清掉原表
insert pludetail
select * from #tempdel --把临时表中无重复的数据插回原表
drop table #tempdel
if @@error<>0
begin
raiserror('数据处理失败!',16,-1)
goto error_deal
end
commit transaction
return
error_deal:
rollback transaction
return
end
/**
要实现以上过程在指定时间内执行
可以用数据库的管理中的作业作实现,很简单,这里不详述了
希望这个方法对你有用
**/