Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Appending data to a table

Hi All,
I'm new to Matillion and I'm experiencing a temporary brain freeze... :)

I need to append data (tbl1) to a table (tbl2). The thing is that the data appended might be present in the destination table. To keep it simple I just need to delete all rows in the destination table tbl2 where date=>X and then append the data from tbl1.
X is given by min(date) in the appended data -tbl1.

Any suggestion how this can be done?

Br
Cris

4 Community Answers

Matillion Agent  

Laura Malins —

Hi Cristian

Table Output will do a simple append but won’t delete. You could use a Table Update component with the Delete/Insert Update Strategy and have a key on the date.

Or it may be simpler to build a Transformation job which takes the input from tbl2 with the filter applied and tbl1. Unite these and write to a temporary table.

Then in another Transformation Job which is to be run immediately afterwards take the temporary table as an input and run that into a Table Output with Truncate on tbl2.

I hope that helps

Thanks
Laura


Cristian Ivanoff —

Thanks Laura!
The filter on tbl2 is min(date) of tbl1. Is there a good way of applying this? What comes to mind is storing min(date) in a variable that I use in the filter of tbl2. But how do I store a value from SQL in a variable?


Matillion Agent  

Laura Malins —

Hi Cristian

Yes you could use a variable for this. Create Orchestration job with the variable v_min_date in it. Use a Python script to run a db cursor to select the min date from tbl1 and then write this to your variable:

cursor = context.cursor()
cursor.execute('select min(date) from tbl1')
date = cursor.fetchone()[0]
context.updateVariable('v_min_date', date)

Then create a date variable in the Transformation Job and pass the value through to the Transformation job in the Run Transform component.

You can then use it in the filter as required.

Thanks
Laura


Cristian Ivanoff —

ok great! I will try this.

Post Your Community Answer

To add an answer please login