游标删除多个表里脏数据的方法
第一种方法:
复制代码 代码如下:
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)
更多相关文章
- python list.sort()根据多个关键字排序的方法实现
- Android(安卓)中文API(86)——ResourceCursorAdapter
- android 当系统存在多个Launcher时,如何设置开机自动进入默认的La
- 【android】监听网络变化连续多个广播的问题解决
- 【有图】android通过jdbc连接mysql(附文件)
- android sqlite 一次创建多个表
- android sqlist中游标下标越界问题解决方案
- android同一个程序中使用多个地图出现混乱怎么办?!
- Android中如何取得联系人,如何得到一个联系人下面的多个号码