Hi! I am loading snapshots from table1 to table2. I want to generate a run id which will be a unique number to identify any job run. So I will have: Table1
ID Name 1 a 2 b 3 c
Table2 (after two consecutive loads, each of which consists of three rows)
ID Name RunID 1 a 1 2 b 1 3 c 1 1 a2 2 2 b2 2 3 c2 2
In this example I loaded Table1 into Table2, then updated Name column for each row of Table1, and then again loaded Table1 into Table2. I want RunID to be generated by target database sequence run_id_seq (PostgreSQL). Could please give me an advise how to accomplish such task? Thanks!
Create a sequence in PostgreSQL and remember its name. Your job must first create the connection to PostgreSQL (tPostgreSqlConnection). The next subjob (OnSubjobOK) should look like this: tPostgreSqlInput_1 --row1--> tFlowToIterate --iterate--> tPostgresqlInput_2 --row2--> tMap --> tPostgresqlOutput. In tPostgresqlInput_1 execute the query "select nextval(your sequence) AS seqid", create one field in the schema named seqid (DB type bigint) and pass the record into the output. Leave tFlowToIterate as it is. Configure tPostgresqlInput_2 to your liking (i.e. query ID and Name). In tMap create the field RunID in the desired output and set ((String)globalMap.get("row1.seqid")) as the expression. Configure tPostgresqlOutput to your liking (i.e. Action on data "Insert" and schema ID, Name and RunID). The target table must not have ID as a key column (in fact none of the columns must be key columns).
Please let us know how this works out for you. Regards, Joachim