Cannot insert duplicate key

I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records.

My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement.

I received this error: Cannot insert duplicate key

Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted I thought this was the default behavior and think that maybe the DBA changed it.

I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.

Try the below query,

INSERT INTO tbUnique

SELECT Distinct * FROM tbDupes

Hi Danny,

Let us assume that the primary key is conformed by (c1, c2, c3), then you can use:

insert into dbo.t1(c1, ..., cn)

select a.c1, ..., a.cn

from dbo.t2 as a

where not exists (

select *

from dbo.t1 as b

where b.c1 = a.c1 and b.c2 = a.c2 and b.c3 = a.c3

)

AMB

Ok. One simple & easy way is adding the identity column temporarily, fetch the data using the following query and drop the temp identity column from the table,