游标删除多个表里脏数据的技巧
发布时间：2021-12-10 09:51:26 所属栏目：教程 来源：互联网
导读：第一种方法： 复制代码 代码如下: 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 c
|
第一种方法： 复制代码 代码如下: 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)  （编辑：我爱故事小小网_铜陵站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330570号