SQL Server uses cursor to process duplicate data.

SQL Server uses cursor to process duplicate data.

/*************************************** * ******************** Code formatted by setyg * Time: 10:04:44 ************************************** * ********************/create proc HandleEmailRepeat as declare email cursor for select e. email, e. orderNo, e. trackingNo FROM Email20140725 AS e WHERE e. [status] = 0 order by e. email, e. orderNo, e. trackingNo begin declare @ email VARCHAR (200), @ fi RstEmail VARCHAR (200), @ FirstOrderNO VARCHAR (300), @ FirstTrackingNO VARCHAR (300), @ NextEmail VARCHAR (200), @ orderNO VARCHAR (300 ), @ NextOrderNO VARCHAR (50), @ trackingNO VARCHAR (300), @ NextTrackingNO VARCHAR (50) begin open email; fetch next from email INTO @ firstEmail, @ FirstOrderNO, @ FirstTrackingNO; fetch next from email INTO @ NextEmail, @ NextOrderNO, @ NextTrackingNO; IF @ NextEmail! = @ FirstEmail begin insert into Email20140725Test (email, OrderNo, TrackingNo) VALUES (@ firstEmail, @ FirstOrderNO, @ FirstTrackingNO); SET @ email = @ NextEmail; SET @ orderNO = @ NextOrderNO; SET @ trackingNO = @ NextTrackingNO; end else begin set @ email = @ NextEmail; SET @ orderNO = @ FirstOrderNO + ',' + @ NextOrderNO; SET @ trackingNO = @ FirstTrackingNO + ',' + @ NextTrackingNO; end fetch next from email INTO @ NextEmail, @ NextOrderNO, @ NextTrackingNO WHILE @ fetch_status = 0 begin if @ NextEmail = @ email begin if (@ NextOrderNO! = @ OrderNO) SET @ orderNO = @ orderNO + ',' + @ NextOrderNO PRINT 'orderno: '+ @ orderNO IF (@ trackingNO! = @ NextTrackingNO) SET @ trackingNO = @ trackingNO + ',' + @ NextTrackingNO PRINT 'trackingNO:' + @ trackingNO end else begin insert into Email20140725Test (email, OrderNo, trackingNo) VALUES (@ email, @ orderNO, @ trackingNO); SET @ email = @ NextEmail; SET @ orderNO = @ NextOrderNO; SET @ trackingNO = @ NextTrackingNO; end fetch next from email INTO @ NextEmail, @ NextOrderNO, @ NextTrackingNO; end close email; -- CLOSE the cursor DEALLOCATE email; -- release the cursor END

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.

**/

In the SQL server database table, how does one Delete duplicate data based on a field?

I used a cursor to implement your function.

First, create an empty table with the same structure as your operation table, but the table must be empty without any content, such as tempReg2.

Copy the following code to the SQL query analyzer and make some modifications.
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
DECLARE Cursor_Title cursor for select distinct title FROM RegMember

OPEN Cursor_Title

Declare @ str varchar (50)
Fetch next from Cursor_Title Into @ str

WHILE @ FETCH_STATUS = 0
BEGIN
Insert into tempReg2 select top 1 * from RegMember where title = @ str
Fetch next from Cursor_Title Into @ str
END

CLOSE Cursor_Title
DEALLOCATE Cursor_Title
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

The table name I used is RegMember and the duplicate column name is title. Therefore, you need to replace these two names. Others can remain unchanged.