Cannot insert duplicate key row in object......

Hi,
I am facing following error in transaction replication.
Cannot insert duplicate key row in object 'TableName'
with unique index 'PKConstraintName
Can any one help on this?
Thanks,
Manoj
Is it the distribution agent that is failing with that error? Then it means
that the subscriber already has a row, but the distribution agent is trying
to insert it again.
Have you been making any changes to the subscribing tables? Could it be that
someone inserted a row to the subscribing table, before that row existed on
the publishing table?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:2e1ad01c46af7$0a435df0$a501280a@.phx.gbl...
> Hi,
> I am facing following error in transaction replication.
> Cannot insert duplicate key row in object 'TableName'
> with unique index 'PKConstraintName
> Can any one help on this?
> Thanks,
> Manoj
>
|||Thanks Vyas,
The subscriber has more number of records than publisher.
Thanks,
Manoj
>--Original Message--
>Is it the distribution agent that is failing with that
error? Then it means
>that the subscriber already has a row, but the
distribution agent is trying
>to insert it again.
>Have you been making any changes to the subscribing
tables? Could it be that
>someone inserted a row to the subscribing table, before
that row existed on
>the publishing table?
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in
message
>news:2e1ad01c46af7$0a435df0$a501280a@.phx.gbl...
>
>.
>

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.