Geek asks and answers

How to remove duplicate rows from a table in SQL Server

I have a table with a name table1that has duplicate values. It looks like this:

new
pen
book
pen
like
book
book
pen

but I want to remove duplicate rows from this table and paste them into another table called table2.

table2 should look like this:

new 
pen
book
like

How to do this in SQL Server?

+5
source share
3 answers

Suppose the field was named name:

INSERT INTO table2 (name)
SELECT name FROM table1 GROUP BY name

This request will provide you with all unique names.

You could even put them in a table variable if you want:

DECLARE @Table2 TABLE (name VARCHAR(50))

INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name

or you can use a temporary table:

CREATE TABLE #Table2 (name VARCHAR(50))

INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name
+1
source

INSERT, SELECT CTE, ROW_NUMBER(), :

DECLARE @YourTable table (YourColumn varchar(10))
DECLARE @YourTable2 table (YourColumn varchar(10))
INSERT INTO @YourTable VALUES ('new')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('like')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')

;WITH OrderedResults AS
(
SELECT
    YourColumn, ROW_NUMBER() OVER (PARTITION BY YourColumn ORDER BY YourColumn) AS RowNumber
    FROM @YourTable
)
INSERT INTO @YourTable2 
        (YourColumn)
    SELECT YourColumn FROM OrderedResults
        WHERE RowNumber=1

SELECT * FROM @YourTable2

:

YourColumn
----------
book
like
new
pen

(4 row(s) affected)

DELETE CTE, ROW_NUMBER(), :

--this will just remove them from your original table
DECLARE @YourTable table (YourColumn varchar(10))
INSERT INTO @YourTable VALUES ('new')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('like')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')

;WITH OrderedResults AS
(
SELECT
    YourColumn, ROW_NUMBER() OVER (PARTITION BY YourColumn ORDER BY YourColumn) AS RowNumber
    FROM @YourTable
)
DELETE OrderedResults
    WHERE RowNumber!=1

SELECT * FROM @YourTable

:

YourColumn
----------
new
pen
book
like

(4 row(s) affected)
+1

I sent something to remove duplicates a couple of weeks ago using DELETE TOP X. For just one set of duplicates. However, in the comments I was given this little pearl of Joshua Patchak.

;WITH cte(rowNumber) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY [List of Natural Key Fields] 
ORDER BY [List of Order By Fields]) 
FROM dbo.TableName)

DELETE FROM cte WHERE rowNumber>1

This will save you all duplicates in the table.
Here is the source post if you want to read the discussion. Duplicate rows in a table.

+1
source

More articles:


All Articles