DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to eliminate duplicates in a table?

Execute the following Microsoft SQL Server T-SQL script to demonstrate how to delete duplicates from a table.

-- Create table with SELECT INTO for testing - Price is increased with $1.00

USE tempdb;

SELECT      ProductID=CONVERT(int, ProductID),

            ProductName = Name,

            ListPrice = ListPrice + 1.00

INTO Product

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0.0

GO

-- (304 row(s) affected)

 

-- Insert full row (line) duplicates

INSERT INTO Product

SELECT      TOP (100) ProductID=CONVERT(int, ProductID),

            ProductName = Name,

            ListPrice = ListPrice + 1.00

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0.0

ORDER BY NEWID()

-- (100 row(s) affected)

SELECT COUNT(*) FROM Product

-- 404

 

------------

-- Eliminate identical duplicates (entire row identical) with SELECT DISTINCT INTO

------------

SELECT DISTINCT *

INTO dedupProduct

FROM Product

GO

-- (304 row(s) affected)

 

------------

-- Eliminate duplicates with GROUP BY

------------

SELECT *

INTO dedupProductGROUPBY

FROM Product

GROUP BY ProductID, ProductName, ListPrice

-- (304 row(s) affected)

------------

 

------------

-- Eliminating / deleting duplicates based on duplicate keys - CTE / ROW_NUMBER

------------

;WITH CTE AS (

     SELECT RN=ROW_NUMBER() OVER (PARTITION BY ProductID

     ORDER BY ModifiedDate DESC )

     FROM Product)

DELETE CTE

WHERE RN > 1

GO

-- (100 row(s) affected)

SELECT COUNT(ProductID) FROM Product

GO

-- 304

 

DROP TABLE tempdb.dbo.Product

DROP TABLE tempdb.dbo.dedupProduct

 

Related article:

 

How to remove duplicate rows from a table in SQL Server

 

SQL SERVER Delete Duplicate Records / Rows by Pinal Dave

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE