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

  • A+
所属分类:MSSQLSERVER


第一种方法:


复制代码 代码如下:


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 '[email protected]
                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)


  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: