Secondary Menu

Main navigation

Write Data In-DB Tool

Version:

Current

Last modified: May 11, 2020

Use the In-DB stream to create or update a table directly in the database.

In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, see In-Database Overview.

Configure the Tool

Append Existing: Appends all the data to an existing table. Output consists of Records Before + Records After.

Delete Data & Append: Deletes all the original records from the table and then appends the data into the existing table.

Overwrite Table (Drop): Completely drops the existing table and creates a new one.

Create New Table: Creates a new table. It does not overwrite an existing table.

Create Temporary Table: Writes to a temporary table that is available until the end of the session. This option is useful for building In-DB predictive macros because it holds the metadata in place temporarily. If this option is selected, the Table Name field is disabled and displays “[a unique temporary table name is generated on each run]".

Update Rows: Updates existing rows in a table based on the incoming records.

Delete Rows: Deletes existing rows in a table based on the incoming records.

Table Name: Enter the name of the database table to create or update.

Append Fields Mapping (or SET Field Map when Update Rows is the selected Output Mode): This area becomes active when Append Existing or Delete Data & Append is chosen above.

Choose the preferred configuration mode.

Auto Config by Name: Aligns fields by field name.

Auto Config by Position: Aligns fields by their field order in the stream.

When Fields are Different: Select how to handle nonconforming data fields from the options using the dropdown.

Options: this area becomes active when Update Rows or Delete Rows is chosen as the Output Mode.

WHERE Field Map: This forms the conditional statement for the Update Rows and Delete Rows Output Mode.

Limitations

Update/Delete Output Options currently do not support column aliases.

Update Output Option currently requires all field names to match the target table's field names, for the Auto Config by Name Append Fields Mapping option, and the number of fields to match the target table's number of fields, for the Auto Config by Position Append Fields Mapping option.

Update/Delete is currently only supported for SQL Server ODBC connections.

Query Structure

Below are sample queries for the Update and Delete Rows options for the Output Mode.