Can you explain how and for what it can be used?
Best answer by tld
View originalAdd custom table insert feature
Userlevel 3
+1
Userlevel 6
+5
Hi
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.
Userlevel 5
+7
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.
Userlevel 3
+1
Hi
Userlevel 1
+1
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.
Userlevel 5
+7
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.
Userlevel 1
+1
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.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
