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.

Code Snippet

INSERT INTO tbUnique

SELECT * FROM tbDupes

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

|||That will not work because my primary key is made up of 3 columns. The remaining 47 columns may make the record unique. However, I only want 1 record for each primary key.|||

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,

Code Snippet

Create Table #duplicates (

[ID1] int ,

[ID2] int ,

[ID3] int ,

[Name1] Varchar(100) ,

[Name2] Varchar(100)

);

Insert Into #duplicates Values('1','1','1','SQLServer','2000');

Insert Into #duplicates Values('1','1','1','SQLServer','2005');

Insert Into #duplicates Values('1','1','2','SQLServer','2005');

Insert Into #duplicates Values('1','1','2','.NET','1.1');

Insert Into #duplicates Values('1','1','3','.NET','1.1');

--Temp Identity Column

Alter table #duplicates add tempid int identity(1,1);

--Required Unique data

select data.id1,data.id2,data.id3,data.Name1,data.Name2 from #duplicates data

Join

(

Select id1, id2, id3, min(tempid) tempid from #duplicates

Group by id1, id2, id3

) required

On required.tempid = data.tempid

--Droping temp identity column

Alter table#duplicatesdrop column tempid

|||Manivannan & Hunchback, thank you for your help.