SQL Server: How can we remove duplicate rows from a table

Answer 1

Suppose you have table1 which has duplicate values and looks like this:

new
pen
book
pen
like
book
book
pen

Now say we want table2 which should look like this:

new 
pen
book
like

It can be achieved as (where table2 can itself be a variable or temp table also)

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

Answer 2

Say your table definition is

MyTable
-----------
RowID int not null identity(1,1) primary key,
Column varchar(20) not null,

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Column
   FROM MyTable 
   GROUP BY Column
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL