SQL Server deletes three methods of repeating records in a data table

This article describes a method for deleting duplicate records in a data table in a SQL Server database.

Project
The Users table in the database, containing U_name,u_pwd two fields, where U_name exists duplicates, now to implement the deletion of duplicates!
Analysis
1, create a temporary table new_users, the table structure is the same as the Users table;
2, the Users table by the ID of a loop, each read a record from the users table, determine whether there is the same u_name in the new_users, if not, then insert it into the new table, if you already have the same item, ignore this record;
3. Change the Users table to another name and rename the New_users table to users to realize our needs.
Program

Declare @id int,@u_name varchar( -),@u_pwd varchar( -)Set @id=1 while @id< +beginif exists(SelectU_name fromUserswhereu_id=@id) beginSelect @u_name=U_name,@u_pwd=U_pwd fromUserswhereu_id=@id --Get Source dataif  not exists(SelectU_name fromNew_userswhereU_name=@u_name)--to determine if there are duplicate u-name itemsbeginInsert  intoNew_users (U_NAME,U_PWD)Values(@u_name,@u_pwd)EndEndSet @id=@id+1EndSelect *  fromNew_users

[Method II]

Assuming that the users table has the same name entry, the ID is the primary key identification field. It is now required to remove duplicate name entries from users.
1. Save the non-duplicate ID in a TMP1 table.
Select MIN ([id]) as [id] into TMP1 from the Users group by [name]

2. From the Users table, select the ID entry in the TMP1 table and write the data of the corresponding ID to the table TMP2
SELECT * Into TMP2 from the Users where [id] in (SELECT [id] from TMP1)

3. Drop the list of users and TMP1 two
drop table Users
drop table Tmp1

4. Rename the TMP2 table to user table
[note] If there is no primary key identification ID, you can add an identity field as follows:
Select Identity (int,1,1) as Autoid, * to tmp0 from Users
[Case three] (script Academy www.jbxue.com)
Suppose there is a user table, ID is the primary key identification field, but there are some completely duplicate items. It is now required to remove these completely duplicated items from users, leaving only one.
1. Keep the non-duplicated data in the TMP1 table
SELECT DISTINCT * to TMP1 from Users

2. Delete the Users table
drop table Users

3. Import the data from the TMP1 table into the Users table
SELECT * to Users from TMP1

4. Delete the TMP1 table
drop table Tmp1

Reference Links:

• example of distinct filtering for duplicate records in SQL Server
• sql Multiple methods for querying duplicate records in server
• sql server repeating records the latest implementation of a pen
• sql server removes duplicate records and only one more example