top button
Flag Notify
Site Registration

delete duplicate rows using informatica

+2 votes

How can we delete duplicate rows from source database table using informatica. This has to be done without using any other table. Means source and target is same table in Oracle database.

posted Sep 24, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

Assuming that the source system is a Relational Database, to eliminate duplicate records, we can check the Distinct option of the Source Qualifier of the source table and load the target accordingly.

enter image description here

answer Sep 24, 2014 by Arun Gowda
Similar Questions
+1 vote

I'm not exactly sure if it will cause an issue or not. But in our environment whenever selecting from large tables we need to include WITH(nolock) otherwise we get deadlocks between select statements and Informatica real time replication trying to populate those tables. Currently we are looking to use SQL jobs to purge old records from tables. Most tables will have less than 1000 rows deleted once a week. During initial purge process there will be several tables that will have millions of records deleted.

Not sure if it would be better to pause real time replication for duration of the purge or just to try to resolve conflicts once those arise.

I don't have a good way to test this in Dev environment because don't have same volume of people hitting server with request, real time updates and everything else that makes up normal load in production. In production this will be running once a week during slow hours that should minimize total load but still don't want to crash Informatica every time this is running.

Note: this question is NOT about how to write delete statements in SQL or breaking delete in small chunks but rather how to avoid conflicts with real time replication with Informatica product.

Database - SQL Server 2012
Replication - Informatica 9.0.1