Skip navigation
Contact Us
1661 Views 6 Replies Latest reply: Dec 10, 2012 11:38 PM by Surya Prakash Reddy RSS
Sri 1236 Apprentice 265 posts since
Mar 21, 2011
Currently Being Moderated

Dec 10, 2012 7:49 AM

Insert else update else delete

Hi all,

 

I need to implement Insert else update else delete logic in my mapping. I know how to implement Insert else update, but I am not aware of implementing the delete logic. Requirement is " Load data from Source (oracle) to target (Sql Server) using Insert else update. Incase if a record is deleted from source system, then I should also be able to delete that record from my target". So, at any point of time, data in source and target would exactly look the same.

 

Please provide some suggestions on this.

 

Thank you folks.

  • Arul SP Newbie 14 posts since
    Oct 26, 2012
    Currently Being Moderated
    Dec 10, 2012 1:11 PM (in response to Sri 1236)
    Insert else update else delete

    Srihari,

     

    Please try this.

     

    pipeline1 - Insert update based on the key column

    pipeline2 - Source Qualifier should have the minus query

                   select * from Target_table

                   minus

                   select * from source_table

             

                   Give DD_DELETE in update strategy transformation.  it will delete the target table records which is not equal to the source table.

     

    Thanks,

    Arul

  • Jason Lim Newbie 12 posts since
    Nov 27, 2012
    Currently Being Moderated
    Dec 10, 2012 5:56 PM (in response to Sri 1236)
    Insert else update else delete

    Arul's suggestion would work just fine. Alternatively you can also do this in a single pipeline by utilizing joiner.

     

    Just add another SQ to pull the key from your target table and perform a full outer join to the existing pipeline. If the source table key is null then set it to DD_DELETE in the update strategy.

  • sasi ramesh Master 1,110 posts since
    Oct 2, 2010
    Currently Being Moderated
    Dec 10, 2012 10:15 PM (in response to Sri 1236)
    Insert else update else delete

    Hi Srihari,

     

    As you are dealing with Different DB.You can implement is using Joiner Transformation.

     

     

    Thanks

    Sasiramesh

  • Surya Prakash Reddy Novice 31 posts since
    Aug 24, 2011
    Currently Being Moderated
    Dec 10, 2012 11:38 PM (in response to Sri 1236)
    Insert else update else delete

    Srihari,

     

    pipeline1 - Insert update based on the key column

     

    pipeline2 - Join source with target fetch target records and not in source records then you can use

               DD_DELETE in update strategy transformation.  it will delete the target table records which is not equal to the source table.

More Like This

  • Retrieving data ...

Bookmarked By (0)