導(dǎo)讀:1建站知識游標(biāo)刪除多個(gè)表里臟數(shù)據(jù)的方法，需要的朋友可以參考一下網(wǎng)站seo優(yōu)化培訓(xùn)網(wǎng)站建設(shè)教程。

第一種方法：

復(fù)制代碼 代碼如下:

CREATE proc [dbo].[delAllRecord]asdeclare @tableName nvarchar(255)declare @Sql nvarchar(255)

Declare curTable Cursor for select Table_Name from information_schema.tables where TABLE_TYPE='BASE TABLE'Open curTableFetch Next From cu營銷型網(wǎng)站建設(shè)rTable Into @tableName

WHILE(@@FETCH_STATUS = 0) BEGIN set @Sql = N'delete from '+@tableName exec sp_executesql @sql Fetch Next From curTable Into @tableName endCLOSE curTableDEALLOCATE curTable

第二種方法：

復(fù)制代碼 代碼如下:

--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的臟數(shù)據(jù)

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的臟數(shù)據(jù)

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

--刪除各個(gè)表里的測點(diǎn)

declare @table_name varchar(50)

declare @sql nvarchar(500)--此處要注意，聲明的長度一定要夠

--declare @measuring_point_id nvarchar(500)

declare del_cursor cursor scroll for

select table_name froseo網(wǎng)站排名優(yōu)化軟件m 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網(wǎng)站seo優(yōu)化課程)

--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)

相關(guān)網(wǎng)站seo優(yōu)化培訓(xùn)網(wǎng)站建設(shè)教程。

關(guān)鍵詞標(biāo)簽: 多個(gè) 游標(biāo)