A simple cursor deletes an SQL SERVER table.

Source: Internet
Author: User

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.

**/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.