how to remove duplicate rows from a table in SQL Server [duplicate]

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?

Answer 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