Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In SQL Server, I can copy one table into another by doing:

Select * into desttable from sourcetable

However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How do I do this?


share|improve this question
The clue is in the question :) – Phil May 16 '12 at 12:47
Very zen, @Phil – JNK May 16 '12 at 13:00
up vote 5 down vote accepted

If they are true EXACT duplicates, you can do:

INTO NewTable
FROM Sourcetable

If they aren't exact dupes in every single field, then we need more info.

share|improve this answer
select distinct *
into YourDestTable
from YourSourceTable

This will select only distinct rows into YourDestTable upon creation.

share|improve this answer

You can perform this activity using ROW_NUMBER() and PARTITION BY clause. This partition clause identified set of duplicate records and row_number assign an ID for this set.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.