Example of deleting duplicate records in a datasheet in SQL Server _mssql

Project

The Users table in the database contains U_name,u_pwd two fields, where u_name duplicates exist, now to delete duplicates!

Analysis

1, generate a temporary table new_users, table structure and the same as the Users table;
2, the users table by ID to do a cycle, each from the users table read a record, to determine whether there is the same u_name new_users, if not, then insert it into a new table; If you already have the same item, ignore this record;
3, to change the users table to other names, the New_users table renamed to users, to achieve our needs.

Program

Copy Code code as follows:

declare @id int, @u_name varchar, @u_pwd varchar (50)
Set @id =1

While @id <1000
Begin
If exists (select U_name from Users where u_id= @id)
Begin
Select @u_name =u_name, @u_pwd =u_pwd from users where u_id= @id--Get source data
If not EXISTS (select U_name from new_users where U_name= @u_name)--to determine if there are duplicate U-name entries
Begin
Insert into New_users (U_NAME,U_PWD) VALUES (@u_name, @u_pwd)
End
End
Set @id = @id +1
End

SELECT * FROM New_users

[Method Two]
assume that the users table has the same name item, and the ID is the field for the primary key. You are now asked to remove the duplicate name entry in the users.

1, the duplicate ID stored in a tmp1 table.

Copy Code code as follows:

Select MIN ([id]) as [id] into TMP1 from Users Group by [name]

2, from the Users table to select the TMP1 table ID entries, the corresponding ID data written to the table TMP2

Copy Code code as follows:

SELECT * into TMP2 from Users where [IDs] in (select [IDs] from TMP1)

3, the users, tmp1 two table drop off

Copy Code code as follows:

drop table Users
drop table Tmp1

4, the TMP2 table renamed as User table

[note] If there is no primary key ID ID, you can add an identity field as follows:

Copy Code code as follows:

Select Identity (int,1,1) as Autoid, * into Tmp0 from Users

[Situation three]
Suppose you have a user table with the ID primary key identifying the field, but there are some completely duplicated items. It is now required to remove these fully duplicated items from the users, leaving only one.

1. Keep the data in the TMP1 table

Copy Code code as follows:

SELECT DISTINCT * into TMP1 from Users

2. Delete the Users table

Copy Code code as follows:

drop table Users

3. Import the data from the TMP1 table to the Users table

Copy Code code as follows:

SELECT * Into the Users from TMP1

4. Delete the TMP1 table

Copy Code code as follows:

drop table Tmp1