how to remove duplicate rows from a table in SQL Server

Question

I have a table called table1 which has duplicate values. It looks like this:

new
pen
book
pen
like
book
book
pen

but I want to remove the duplicated rows from that table and insert them into another table called table2.

table2 should look like this:

new 
pen
book
like

How can I do this in SQL Server?

You used `c#` tag for this question because..? And what have you tried so far? Read [FAQ] and [ask] — Soner Gönül, Feb 27 '13 at 18:50
This question is not a duplicate of the linked question because a. unlike the linked question this table has no primary key (which negates most of the solutions in the linked question), and b. it is transferring data to another table. — user281806, Oct 12 '13 at 00:46

score 1 · Accepted Answer · answered Feb 27 '13 at 18:51

1

Let's assume the field was named name:

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

that query would get you all the unique names.

You could even put them into a table variable if you wanted:

DECLARE @Table2 TABLE (name VARCHAR(50))

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

or you could use a temp table:

CREATE TABLE #Table2 (name VARCHAR(50))

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

answered Feb 27 '13 at 18:51

Mike Perrenoud

66,820
29
157
232

"remove the duplicated rows from that table" – Magnus Feb 27 '13 at 18:52
how can i create table2 with them ???@Michael – meo Feb 27 '13 at 18:52
@Magnus: you're right. It feels like there was an edit in between there - I didn't see that in the first and even second read. – Mike Perrenoud Feb 27 '13 at 18:53
@MichaelPerrenoud Yeh, you are probably right. – Magnus Feb 27 '13 at 18:54

score 1 · Answer 2 · answered Feb 27 '13 at 19:04

You can do this easily with a INSERT that SELECTs from a CTE where you use ROW_NUMBER(), like:

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

OUTPUT:

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

(4 row(s) affected)

You can do this easily with a DELETE on a CTE where you use ROW_NUMBER(), like:

--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

OUTPUT:

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

(4 row(s) affected)

score 1 · Answer 3 · answered Feb 27 '13 at 19:07

I posted something on deleting duplicates a couple of weeks ago by using DELETE TOP X. Only for a single set of duplicates obviously. However in the comments I was given this little jewel by 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 get rid of all of the duplicates in the table.
Here is the original post if you want to read the discussion. Duplicate rows in a table.

how to remove duplicate rows from a table in SQL Server

3 Answers3