Insert then Update

nima511 · Participant Joined: 08 Oct 2009 Posts: 20 Points: 145

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 · Posted: Wed Jul 28, 2010 10:42 am

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 · Participant Joined: 08 Oct 2009 Posts: 20 Points: 145

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 · Posted: Wed Jul 28, 2010 3:01 pm

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 i ...

What's this?

nima511 · Participant Joined: 08 Oct 2009 Posts: 20 Points: 145

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

ArndW · Posted: Thu Jul 29, 2010 3:08 am

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 ...

What's this?

nima511 · Participant Joined: 08 Oct 2009 Posts: 20 Points: 145

Exactly.