I have the same issue on some tables that have an auto increment column which isn't an identity column, though lucky it sounds like it doesn't happen as often as it does for you! This is my technique which doesn't use a temp table, I use set rowcount set to 1 less than the number of duplicates and then call delete:

CREATE TABLE dbo.tblDupTest( id int not null,)

INSERT INTO dbo.tblDupTest VALUES(1)INSERT INTO dbo.tblDupTest VALUES(1)INSERT INTO dbo.tblDupTest VALUES(2)INSERT INTO dbo.tblDupTest VALUES(3)INSERT INTO dbo.tblDupTest VALUES(4)INSERT INTO dbo.tblDupTest VALUES(4)INSERT INTO dbo.tblDupTest VALUES(4)

-- At this point we should have two 1s and three 4sSELECT *FROM dbo.tblDupTest

-- This will give us the countsSELECT id, COUNT(id) AS 'Count'FROM dbo.tblDupTestGROUP BY idHAVING COUNT(id) > 1

-- Then set the rowcount to one less than the duplicate and call deleteset rowcount 1

It's very neat and all, and maybe I misunderstand, but if the tables are identical, couldn't you avoid the whole duplicates issue by inserting into one table only, and let replication take care of the rest?

yes agree with this - if you are replicating two databases then just update one - other than that use unique constraints (and if need be triggers) to make sure that you always have a natural uniqueness on each row in your table.