Can you explain how and for what it can be used?
Best answer by tld 27 February 2023, 10:52
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.
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.