Add custom table insert feature

Hi @rvgfox 

Please see this article about custom table inserts. It is used to insert data into a table based on a custom SQL script. For example, the below shows a custom insert based on a union statement from three tables.

Note that Custom Table Inserts do not keep lineage (you would need to use Advanced > Object Dependencies). It is usually better to use a view in the layer below or same layer as those can keep lineage intact.

Hi @Christian Hauggaard Can you explain for what we can use this feature?

There are several use-cases. 

Here is one that i used it for recently: I had a table that contained a primary key, a timestamp, a status (and other fields). I only need the last record for every Primary Key for each day. I made a custom table insert for that. I could also have made a custom view, but with the custom table insert I could implement the rest of the business logic the TimeXtender-way instead of doing T-SQL in the view.

I would still use Custom Views in that scenario: only use the Custom View for the selection of the last record per key. You can drag any Custom View onto the Tables node to make a physical table out of it and do any regular transformations there. The filling of that table is done through the view.

Alternatively you can use a Query Table on the source to do the selection logic there and remove the need for one of the physical tables.

I tend to avoid Query tables containing busines logic in order not to put any additonal load on the source system. As for the custom view approach, you would get a regular table insert. I like the Custom Table insert so that I have the code with the table instead of having to navigate to Views to find it.