Insert then Update

[nima511]

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.

[ArndW]

Why not have two output stages in one job? Could you specify an example of what you want to do in a couple of words to explain the problem?

[nima511]

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.

[Sainath.Srinivasan]

Is the ID generated in the job or part of sequence in the db ?

Why must there be two table having same data ?

Can you provide bit more info.

Maybe you can include a db trigger in A to initiate insert into B.

[nima511]

ID is being generated in the transformer.
It is a requirement thats why we need to populate the ID COlumn in both tables.

[ArndW]

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.

[nima511]

Exactly.