Delete duplicate records from table in SQL Server

USE master
--====== CREATE Temporary TABLE
Create table #tblTesting (name varchar (100), Email varchar (100))

--====== INSERT DATA INTO TABLE
INSERT INTO #tblTesting
 VALUES ('TestEmail', 'TestEmail@gmail.com')
INSERT INTO #tblTesting
 VALUES ('TestEmail1', 'TestEmail1@gmail.com')
INSERT INTO #tblTesting
 VALUES ('TestEmail1', 'TestEmail1@gmail.com')
INSERT INTO #tblTesting
 VALUES ('TestEmail2', 'TestEmail2@gmail.com')
INSERT INTO #tblTesting
 VALUES ('TestEmail2', 'TestEmail2@gmail.com')
INSERT INTO #tblTesting
 VALUES ('TestEmail3', 'TestEmail3@gmail.com')
--====== TIPS 1 TO DELETE DUPLICATE RECORDS
WITH Get_rownumber as
(
SELECT
 name,
 email,
 ROW_NUMBER() OVER (ORDER BY name, Email) AS rownumber
FROM #tblTesting

)
DELETE Get_rownumber
WHERE ROWnumber NOT IN (SELECT
  MIN(rownumber)
 FROM Get_rownumber
 GROUP BY name,
    email)

--====== TIPS 2 TO DELETE DUPLICATE RECORDS
WITH Get_Duplicate (name, email, duplicate_Count) as

(SELECT
 name,
 email,
 ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY name, email) AS duplicate_Count
FROM #tblTesting)

DELETE FROM Get_Duplicate
WHERE duplicate_Count > 1
--====END==================
SELECT  * FROM #tblTesting

DROP TABLE #tblTesting

Comments

Post a Comment

Popular posts from this blog

What is Aws?

Introduction to Agile development