One Star

Populating cross reference table

Hi all,
I have got a situation, where i have to create a table in the staging for look up purpose.
Here is the scenario.....Lets take Table A as source table, Table B as Target table and Table C as staging table.
** ID column of Table B is Identity column.
Table A                                                                             Table B

-----------------------------------------                                       ---------------------------
|    A_Code   |     Description       |                                       |       ID   |    B_Code   |
-----------------------------------------                                        ---------------------------
|         a   |     Australia              |                                       |         1   |       a      |
-----------------------------------------                                       ---------------------------
|         g   |     Germany              |                                       |         2   |       g      |
-----------------------------------------                                       ---------------------------

Table C
--------------------------------------------------------
|       A_Code   |       ID      |     Description    |
--------------------------------------------------------
|         a           |      1         |  Australia          |
---------------------------------------------------------  
|         g           |      2         |  Germany          |
---------------------------------------------------------
So first we insert one record from table A to Table B, the inserted record will generate an id (as ID is Identity column) , that id should be inserted in the staging table C with it corresponding Table A related values. Table C in staging should look like above, in my job i am not able to retrieve the last inserted record inserted in Table B.
I tried by taking Max of the Table B but didn't worked some mismatch happend when testing with different scenario.
I also tried tMSSQLLastInserted but its throwing exception(NullPointerException).
Can anybody help me to achieve this??
1 REPLY
Moderator

Re: Populating cross reference table

Hi pats.7pk,
Did you use tMap to join your source table A and staging table C? Could you please show us your current job design?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.