1. Bods Table Comparison Generated Key Columns
2. Bods Table Comparison Generated Key Column Numbers
3. Bods Table Comparison Generated Key Column List
4. Bods Table Comparison Generated Key Column Examples

Hello Experts,

Jun 28, 2012  If the input data set and the comparison table both have the column you specified in Generated key column, the transform does not compare the values for this column; it preserves the value. Without his option: If your comparison table contains rows with the same primary keys, the transform arbitrarily chooses which of these rows to compare. A query to filter (using where) and change values (using mapping). And then a Table Comparison (where I expected those lines to be set to update, in this particular case), set table name on first entry, then PK in 'input primary key' and then the two columns I want to change in 'Compare columns'. No other changes from default that I can recall. Apr 23, 2019 This video explains about GENERATED KEY COLUMN feature of table comparison. Visit the below tutorials before viewing this: Basics of Table Comparison: https. Aug 22, 2016  1) In the row-by-row mode, the Table Comparison Transform executes a select statement for every single input row to lookup the value in the compare table. 2) In sorted input mode, we guarantee that the data is coming sorted by the columns listed as primary key columns.

1. Now link both the table and the RowGeneration to the Query transform, Now click on the query and open it. Drag all the fields from the table under schema in to the schema out. Select the top field on the schema out and right click and click on “ New output column”, Click on “ Insert above”. Name the new output column as RGNO.
2. A generated key column indicates which row of a set containing identical primary keys is to be used in the comparison. This provides a method of handling duplicate keys in the comparison table. For an UPDATE, the output data set will contain the.

Adding further concepts to my knowledge I was working on Generated Key column of Table Comparison, placing the common column existing in both source and target and incoming source has duplicates. So below is my observation please have a glimpse of it!

TABLE COMPARISON: – Generated Key Column

When records coming from source has duplicates for column(s) defined in Input Primary Key Columns section and out of those columns one is selected in Generated key column then records inserted in target are random. So in brief I’ve selected the column which is present in both source and target. Here it’s EMP_ID.

How table comparison sends the records to the final target when this option is selected?

Initially my target table has data present in it. When job is executed then this transform brings the rows to it’s before image for processing and then comparison starts. Based on the OPCODES generated the rows are sent to final target table.

Here I put this brief note in below screenshots: –

Consider the initial target before job was executed: –

Generated key column value has EMP_ID column, which has duplicates, selected in table comparison: –

Initial source records before job is executed: –

Now after execution,

When the job is executed table comparison brings the records in it’s before image. Initially target table has existing records for EMP_ID 1010 (1010, RAJ WRITER,..) and 1011 (1011, RAJ MCKINSEY,…). Now records coming from source are compared one by one.

Firstly EMP_ID 1010 (1010, RAJ WRITER,..) is compared with existing id, 1010, RAJ WRITER,.., table comparison finds no change/update, hence no opcode is generated. Then incoming EMP_ID 1011 is compared with existing id, 1011, RAJ MCKINSEY,…, and again no opcode is generated. Now when EMP_ID 1010 (1010, RAJ,… ) comes from source its compared with existing id, 1010, RAJ WRITER,.., Table comparison detects that EMPNAME has been changed from RAJ WRITER to RAJ, hence it sends an update, ’U’, opcode. Similar is the case with EMPNAME RAJ EY for EMP_ID 1011.

So after execution: –

Records in final Target: –

This was the case when we had only one duplicate record coming from source.

Bods Table Comparison Generated Key Columns

Now let’s suppose that I add few more duplicates in my source for the EMP_ID’s which are present in target. Then how table comparison works, let’s see.

I added 3 more records for EMP_ID 1010 and 1011 in my source. So my new source becomes: –

Before execution my target table is: –

When job gets executed then for all the existing id’s which are already present in target an update, ‘U’, is sent by Table comparison: –

Now which record will be sent by our before image to the target?

For EMP_ID 1010, 4^th record, with EMP_ID 1010, is sent to target table and for EMP_ID 1011 1^st record, with EMP_ID 1011, is sent as displayed in above screenshot.

Again if I re-execute the same job then table comparison should work in a similar fashion: –

As per above observation it should send 4^th update for EMP_ID 1010 and 1^st update for EMP_ID 1011, right?

So the final target is: –

Bods Table Comparison Generated Key Column Numbers

This time table comparison sent 1^st update for EMP_ID 1010 and 1^st update for EMP_ID 1011.

This is strange! 🙁

Now again if you’ll run the same job then again it’ll give different outputs and different records will be sent to target.

So this means if you use same column, present in source and target and column defined in Input Primary Key column has duplicate incoming rows, in Generated Key Column section then table comparison will randomly send the updated records to the target table.

So if the column(s) specified in Input Primary Key columns section has duplicate incoming records and out of them if same column is selected in Generated Key column then table comparison will arbitrarily send the records to the target.

To further understand how does records get processed by Table comparison you can visit this link: –

Please let me know if I’ve missed anything or something is incorrect.

Bods Table Comparison Generated Key Column List

Hope it helps!

Bods Table Comparison Generated Key Column Examples

Thanks 🙂