SQL Server clears completely duplicate data and only keeps the first item in the duplicate data

Time:2021-10-23
--Create test table 
CREATE TABLE [dbo].[testtab]( 
[id] [nchar](10) NULL, 
[name] [nchar](10) NULL 
) ; 
--Insert test data into the test table 
insert into testtab values('1','1'); 
insert into testtab values('1','1'); 
insert into testtab values('2','2'); 
insert into testtab values('2','2'); 
insert into testtab values('3','3'); 
insert into testtab values('3','3'); 

--Create a temporary table, insert the data in the test table testtab into the temporary table, and add self incrementing ID: AutoID 
select identity(int,1,1) as autoID, * into #Tmp from testtab 
--Delete the duplicate data in the temporary table #tmp according to AutoID, and keep only the first item in each group of duplicate data 
delete #Tmp where autoID in(select max(autoID) from #Tmp group by id); 
--Clear all data in testtab table 
delete testtab; 
--Insert #tmp processed data into testtab table 
insert into testtab select id,name from #Tmp; 
--Delete temporary table #tmp 
drop table #Tmp;