Finding Duplicate data existing in Database

A good starting point is finding a SQL implementation - it will give you an idea of what’s possible on your database.

For instance in PostgreSQL you would want to use INSERT … ON CONFLICT (product_id) … however the only options seem to be DO NOTHING or DO UPDATE SET …. It’s not possible to raise an error (which would kill the command) or run an arbitrary stored procedure.

Instead you would need to use something like this

WITH both as (SELECT id FROM TABLE table1 INTERSECT SELECT id FROM TABLE2)
INSERT id, value INTO table VALUES (SELECT id, value FROM table2 WHERE id NOT IN both.id) AS X;
INSERT * INTO duplicates FROM (SELECT * FROM table2 WHERE id IN both.id) AS X;

(that’s off the top of my head - there may be small syntax errors.)

This is why snaps don’t always implement “obvious” features like writing duplicates to an error view. We want to keep the database snaps as consistent as possible and sometimes a feature that is easy to implement on one database would require a lot of work under the covers on other databases.

Please help with this @darshthakkar , @AleksandarAngelevski

Thanks in Advance

Here’s a simple script that will return all duplicate product_id and partner product_id combination

select	a.part_prod_id, a.num_row 
from	(
			select	concat([parter product id], [product id]) as part_prod_id, count(concat([parter product id], [product id])) as num_row
			from	yourtable (nolock) 
			group	by concat([parter product id], [product id])
		) a
where	a.num_row > 1

Thankyou @alchemiz and @bgiles for responding