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?
4 Community Answers
Laura Malins —
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.
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?
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()
Then create a date variable in the Transformation Job and pass the value through to the Transformation job in the Run Transform component.