How to delete dirty data in multiple tables with a cursor

Method 1:

Copy codeThe Code is as follows:

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

Method 2:

Copy codeThe Code is as follows:

-- Declare test_cursor cursor scroll

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

 

-- Delete dirty data of 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)

 

-- Delete dirty data of section_settings

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

 

-- Delete the measurement points in each table

Declare @ table_name varchar (50)

Declare @ SQL nvarchar (500) -- Note that the declared length must be sufficient.

-- Declare @ measuring_point_id nvarchar (500)

Declare del_cursor cursor scroll

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)