Write Data In-DB Tool

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

Output Mode: Select the appropriate option for writing the data. Choices include:

Append Existing: Appends all the data to an existing table. Output will consist 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. Note that this mode is different depending on the database you write to:
- Oracle Databases: Uses DELETE statement.
- SQL Server Databases: Use TRUNCATE TABLE statement because this is a more efficient method. You need either the ALTER table or TRUNCATE permissions on the table.
Overwrite Table (Drop): Completely drops the existing table and creates a new one.
Create New Table: Creates a new table. Will 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. Choices are:

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 drop-down.

Error - Stop Processing: will throw an error in the Results window, and end processing.
Output Applicable Fields: Applicable fields will be included. Null values will populate empty fields.

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.
Update/Delete Output options currently do not support updating or deleting Null values. The comparison operators (such as = or !=) being used for Alteryx update and delete queries return “unknown” for null values. For more information, see Microsoft SQL Server documentation.

Query Structure

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

Update Rows


	WITH upstream AS
	(
	...
	)
	UPDATE t
	SET
	t.col2 = (SELECT col2 FROM upstream u WHERE u.col1 = t.col1 AND ...),
	...
	FROM target_ t
	WHERE EXISTS
	(
	SELECT * FROM upstream u WHERE u.col1 = t.col1 AND ...
	)

Delete Rows


	WITH upstream AS
	(
	...
	)
	DELETE t from target_ t
	WHERE EXISTS
	(
	SELECT * FROM upstream u WHERE u.col1 = t.col1 AND ...
	)