Let's take a look at how you can use it to delete duplicate records in a table:
Copy Code code as follows:
If Exists (Select * FROM Tempdb.Information_Schema.Tables Where table_name like ' #Temp% ')
Drop Table #temp
Create Table #temp ([Id] int, [Name] varchar, [age] int, [SEX] bit default 1)
Go
Insert into #temp ([Id], [Name], [age], [Sex]) Values (1, ' James ', 25,default)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (1, ' James ', 25,default)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (1, ' James ', 25,default)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (2, ' Lisa ', 24,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (2, ' Lisa ', 24,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (2, ' Lisa ', 24,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (3, ' Mirsa ', 23,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (3, ' Mirsa ', 23,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (3, ' Mirsa ', 23,0)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (4, ' John ', 26,default)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (5, ' Abraham ', 28,default)
Insert into #temp ([Id], [Name], [age], [Sex]) Values (6, ' Lincoln ', 30,default)
Delete T from
(Select row_number () over (Partition by [id],[name],[age],[sex] ORDER by [ID]) as rownumber,* from #Temp) T
Where t.rownumber > 1
Select * from #temp
Note the penultimate script, which we implement in a query.
You can perform your own T-SQL script to see the effect. Hope to help you develop!
