How to Insert and Delete the rows in the same Table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
How to Insert and Delete the rows in the same Table
Hi,
How to Insert and Delete the rows in the same Table within a single job.
Please clarify the issue.
For example let assume SRC as source table and TRG as Target Table. I want to Insert and Delete in the TRG table according the SRC table changes.
Advanced Thanks,
Reddy.
How to Insert and Delete the rows in the same Table within a single job.
Please clarify the issue.
For example let assume SRC as source table and TRG as Target Table. I want to Insert and Delete in the TRG table according the SRC table changes.
Advanced Thanks,
Reddy.
This is a typical DataStage task and often done. You can use the same stage canvas object for the insert and the delete by having two links into it, or you can use two separate stages. The logic of which row gets sent down which link is done with constraints in a transform stage.
What logic have you tried so far that hasn't worked, if you explain that we might be able to give targeted assistance.
What logic have you tried so far that hasn't worked, if you explain that we might be able to give targeted assistance.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
Hi,
Thanks for immediate reply.
The scenario is
In the constraints part of the Transform stage I mentined as
If A_ACT='A' or 'U' then it should be Insert in the Target
and If A_ACT='D' or 'B' it should be Delete in the Target
Means if source side record Inserts or update it should be insert and if source side records deletes it should be delete in the target.
Regards,
Reddy.
Thanks for immediate reply.
The scenario is
In the constraints part of the Transform stage I mentined as
If A_ACT='A' or 'U' then it should be Insert in the Target
and If A_ACT='D' or 'B' it should be Delete in the Target
Means if source side record Inserts or update it should be insert and if source side records deletes it should be delete in the target.
Regards,
Reddy.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Supply only the key column(s) on the link that looks after DELETE operations. Use user-defined SQL of the form
Code: Select all
DELETE FROM tablename WHERE keycol = '?';
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
No specific options. The 'fix' is to simply use one target stage instead of two so they are aware of each other and run in the same transaction - this will resolve your deadlock issue.
As long as your constraints and update actions are fine, having both links run into a single stage should be the only change you need to make.
As long as your constraints and update actions are fine, having both links run into a single stage should be the only change you need to make.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers