当前位置： 移动技术网 > IT编程>数据库>MSSQL > 游标删除多个表里脏数据的方法

游标删除多个表里脏数据的方法

2017年12月12日  | 移动技术网IT编程  | 我要评论

灰姑娘丶梧桐,tda16846,b型h系下载

第一种方法：

复制代码 代码如下:

create proc [dbo].[delallrecord]
as
declare @tablename nvarchar(255)
declare @sql nvarchar(255)

declare curtable cursor
        for select table_name from information_schema.tables where table_type='base table'
open curtable
fetch next from curtable into @tablename

while(@@fetch_status = 0)
        begin
                set @sql = n'delete from '+@tablename
                exec sp_executesql @sql
                fetch next from curtable into @tablename
        end
close curtable
deallocate curtable


第二种方法：

复制代码 代码如下:


--declare test_cursor cursor scroll for

--select id,table_name from dbo.section_type

--open test_cursor

--declare @id int

--declare @table_name nvarchar(50)

--while @@fetch_status=0

--begin

--fetch next from test_cursor into @id,@table_name

--print @id

--print @table_name

--end

--close test_cursor

--deallocate test_cursor

 

--删除projectrangtree的脏数据

delete from projectrangtree where deleteversion>0

delete from projectrangtree where type=3 and parentid not in(select id from projectrangtree where type=2)

delete from projectrangtree where type=4 and parentid not in(select id from projectrangtree where type=3)

delete from projectrangtree where type=5 and parentid not in(select id from projectrangtree where type=4)

 

--删除section_settings的脏数据

delete from section_settings where parent_prj_tree_id not in(select id from projectrangtree)

 

--删除各个表里的测点

declare @table_name varchar(50)

declare @sql nvarchar(500)--此处要注意，声明的长度一定要够

--declare @measuring_point_id nvarchar(500)

declare del_cursor cursor scroll for

select table_name from section_type

open del_cursor

fetch next from del_cursor into @table_name

--print @table_name

while (@@fetch_status=0)

begin

--print quotename(@table_name)

--set @measuring_point_id='select measuring_point_id from '+quotename(@table_name)

--exec sp_executesql @measuring_point_id

set @sql = 'delete from '+ quotename(@table_name) +' where measuring_point_id not in(select id from measuring_point_setting)'            

exec sp_executesql @sql

--delete from @table_name where measuring_point_id not in (select id from measuring_point_setting)

fetch next from del_cursor into @table_name

end

close del_cursor

deallocate del_cursor
 

--delete from (select talbe_name from section_type) where measuring_point_id not in (select id from measuring_point_setting)

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网