Delete Rows from target table

hema177 · Post by **hema177** » Wed Sep 06, 2006 2:06 am

Hi,

Is there any way in which rows which no longer exist in the Source tables can be deleted from the target tables using logic inherent to Datastage?

We are running server jobs on the Datastage Enterprise 7.5.1.A edition.

Thanks,

ArndW · Post by **ArndW** » Wed Sep 06, 2006 2:10 am

Sure you can, it is quite easy. One easy way (but not necessarily the fastest for huge data volumes): Do a lookup from the target to the source; if the lookup is not successful then output to a stage which deletes those rows from the target.

hema177 · Post by **hema177** » Wed Sep 06, 2006 6:28 am

ArndW wrote:Sure you can, it is quite easy. One easy way (but not necessarily the fastest for huge data volumes): Do a lookup from the target to the source; if the lookup is not successful then output to a stage which deletes those rows from the target.

Thanks for this... Will try this out.

keshav0307 · Post by **keshav0307** » Wed Sep 06, 2006 7:20 am

You can use change capture stage.

rasi · Post by **rasi** » Wed Sep 06, 2006 6:06 pm

Change capture is in Enterprise.

Why not re-create the Target table instead of deleting. You can use bulk loader to load the target again.

kumar_s · Post by **kumar_s** » Wed Sep 06, 2006 7:18 pm

To find the records which are not in Source, you need to do a look, it can be either through Lookup Stage or Join stage as Arnd saind or recreate a table thorough a query INSERT INTO ....(SELECT ... FROM <target> IN(SELECT ... FROM <source>)) as suggested by Siva.

hema177 · Post by **hema177** » Mon Sep 11, 2006 5:21 am

Hi All,

Thanks for all the suggestions, we have had to redesign all the maps to run in Enterprise, so we'll be using the Change Capture stage to deal with the deletes.

Cheers..