Duplicate values are processed when SQL deletes duplicate records do not have a size relationship

-->--> (Roy) generation

if not object_id (' tempdb ... #t ') is null
drop table #t
Go
CREATE TABLE #t ([num] int,[name] nvarchar (1))
Insert #t
Select 1,n ' A ' union ALL
Select 1,n ' A ' union ALL
Select 1,n ' A ' union ALL
Select 2,n ' B ' UNION ALL
Select 2,n ' B '

Go

Method 1:

If object_id (' tempdb.. # ') is not null
DROP TABLE #
SELECT DISTINCT * into # from #t--Exclude duplicate record result set generate temporary table #

TRUNCATE TABLE #t--Emptying tables

Insert #t SELECT * FROM #--Inserts temporary table # into table #t

--View Results
SELECT * from #t

/*
Num Name
----------- ----
1 A
2 b

(2 rows affected)
*/

--Use Method 2 after the test data is rerun
Method 2:

ALTER TABLE #t add ID int identity--new identity column
Go
Delete A from #t a where exists (select 1 from #t where Num=a.num and Name=a.name and id>a.id)--keep only one record
Go
ALTER TABLE #t drop column id--Delete an identity column

--View Results
SELECT * from #t

/*
Num Name
----------- ----
1 A
2 b

(2 rows affected)

*/

--Use Method 3 after the test data is rerun
Method 3:

Declare roy_cursor cursor Local for
Select COUNT (1) -1,num,name from #t GROUP by Num,name has count (1) >1
declare @con int, @num int, @name nvarchar (1)
Open Roy_cursor
FETCH NEXT from Roy_cursor into @con, @num, @name
While @ @fetch_status =0
Begin
SET ROWCOUNT @con;
Delete #t where num= @num and name= @name
SET ROWCOUNT 0;
FETCH NEXT from Roy_cursor into @con, @num, @name
End
Close Roy_cursor
Deallocate roy_cursor

--View Results
SELECT * from #t
/*
Num Name
----------- ----
1 A
2 b

(2 rows affected)
*/
Using Stored Procedures

Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) > 1
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Begin
Select @max = @max-1
SET ROWCOUNT @max
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
SET ROWCOUNT 0

Working with functions

SELECT DISTINCT * into #tmp tablename
DROP TABLE TableName
SELECT * INTO TableName from #tmp
drop table #tmp