A simple cursor deletes an SQL SERVER table.
Use databaseName
Declare @ tblname char (100)
Declare @ SQL char (5000)
Declare table_cursor cursor for select name from sysobjects where name like 'tb _ card [_] % 'and name <> 'tb _ card_regist' and name <> 'tb _ card_item 'and name <> 'tb _ card_discrule' and name <> 'tb _ card_packagesetdetail 'and name <> 'tb _ card_packagedetail' and name <> 'tb _ card_exchangeitem'
And name <> 'tb _ card_package'
Open table_cursor
Fetch next from table_cursor into @ tblname
WHILE @ FETCH_STATUS = 0
BEGIN
Set @ SQL = 'delete from' + @ tblname
Exec (@ SQL)
Print 'delete' + @ tblname + 'successful'
Fetch next from table_cursor into @ tblname
END
Close table_cursor
Deallocate table_cursor
SQL defines a cursor to delete the first row of data in the student table.
Declare @ sex int
Declare @ grade int
Declare my_youbiao cursor
For select sex, grade from StudentTable
Open my_youbiao
Fetch next from my_youbiao into @ sex, @ grade
While @ fetch_status = 0
Begin
If @ sex = 'male'
Begin
Update StudentTable set grade = @ grade-'2' where current of my_youbiao
End
Else
Begin
Update StudentTable set grade = @ grade-'1' where current of my_youbiao
End
Fetch next from my_youbiao into @ sex, @ grade
End
Close my_youbiao
Deallocate my_youbiao
How can I write a stored procedure in SQL Server to delete duplicate records in a table?
/*******
Assume that the name of the table to be processed is pludetail.
The following process can be used to achieve the speed. The speed is not considered in the following process.
*********/
Create procedure distinct_deal
As
Begin
Begin transaction
Select distinct * into # tempdel from pludetail -- extract non-repeated records to the temporary table
Truncate table pludetail -- clear the original table
Insert pludetail
Select * from # tempdel -- insert non-duplicated data in the temporary table back to the original table
Drop table # tempdel
If @ error <> 0
Begin
Raiserror ('Data Processing failed! ', 16,-1)
Goto error_deal
End
Commit transaction
Return
Error_deal:
Rollback transaction
Return
End
/**
The above process must be executed within the specified time
You can use jobs in database management for implementation. This is very simple and will not be detailed here.
Hope this method is useful to you.
**/