Custom Table Inserts allow users to add data to a table based on a custom SQL query and store the output in the table it is added on.
To use this feature, right-click the table you want to add data to, select Advanced,and then select Add Custom Table Insert.
In the script editor, create a SQL SELECT statement - no INSERT clause is necessary. The script should look like this:
SELECT /FirstName], /MiddleName], /LastName], /FullName] AS AFull Name] FROM OPerson]
If the field names in the source tables are different from the field names in the destination table table, use an alias to make the names match up.
The benefit is that you can use this in all locations you would use a insert into script action, all you need is to be sure the select statement ends up with the same field names and data types.
Page 1 / 1
Hi @Thomas Lind The script can be a call to an store procedure?
Yes, as I do in this script.
@Thomas Lind And, can you use a “exec xuspStoreProcedure]” sentence that returns the records?
Yes
But if the fields of the table you put it in are different than the source it will not work as the names doesn’t exist. That is why I have the select statement in the other script.
@Thomas Lind What is the diference to select as destination table Raw or Valid?
I supose that if we select Raw, we will can to use transformations in TX isn’t it?
Yes, if you set it to raw it will be affected by the data cleansing procedure, whereas if you set it to valid it will not. It is only very rare occasions it will ever be necessary to not add it to raw, but there could be some instances where you want to update a field or similar after the cleansing have been done.
@Thomas Lind I’ve detected a issue with this feature. If we use temporal tables either in the code or in a store procedure, we get an error like this: