Hello,
I want to perform a Insert into one table (A) and based upon the Insert values in the first table (A) Update the second table (B).
Can I accomplish this using one stage like the ODBC or DRS Stage instead of 2 seperate jobs?
Database that I am using is SQL Server.
Insert then Update
Moderators: chulett, rschirm, roy
My job looks like:
File -> Xfrm -> ODBC or DRS Stage
Table A has ID, Name Columns
Table B has ID Column.
In the ODBC or DRS Stage: I want to first insert records into Table A. After populating Table A I want to insert records into Table B based on the records that have been inserted into Table A (ID) Column .
I tried using the DRS Stage by using the After tab for inserting into Table B as below:
INSERT INTO B (ID)
SELECT ID FROM A
ORDER BY ID;
This works fine. But the only thing is that DRS Stage does not have a reject link to show how many records have been inserted and how many have been rejected.
File -> Xfrm -> ODBC or DRS Stage
Table A has ID, Name Columns
Table B has ID Column.
In the ODBC or DRS Stage: I want to first insert records into Table A. After populating Table A I want to insert records into Table B based on the records that have been inserted into Table A (ID) Column .
I tried using the DRS Stage by using the After tab for inserting into Table B as below:
INSERT INTO B (ID)
SELECT ID FROM A
ORDER BY ID;
This works fine. But the only thing is that DRS Stage does not have a reject link to show how many records have been inserted and how many have been rejected.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
If I understand your issue, you want to ensure that if records going to A are rejected during the write phase that they don't go into B?
In that case, you can put a reject link out of A and then make that the reference stream to a lookup stage coming out of the transform stage and heading for B. All rejects to that lookup are records that have successfully been written to A and need to be written to B. This assures that the writes to B only happen after all writes to A have occurred.
In that case, you can put a reject link out of A and then make that the reference stream to a lookup stage coming out of the transform stage and heading for B. All rejects to that lookup are records that have successfully been written to A and need to be written to B. This assures that the writes to B only happen after all writes to A have occurred.