User Tools

Site Tools


transformations:sqlcommand

SQL Command

Performs one of these 4 commands:

  • Create database table
  • Delete database table
  • Delete all rows
  • Custom command

Create database table

Creates a new database table. The table name can be specified either explicitly, or using a project parameter.

Field types are automatically suggested based on the input dataset, but can be changed manually. Field names are taken from the input dataset. They are case-sensitive.

Delete database table

Deletes a database table and all data in it. The table name can be specified either explicitly, or using a project parameter.

Be careful when deleting database tables as this operation can't be undone. All information in the target database table is lost forever.

Delete all rows

Makes a database table empty by removing all data (rows) from it. The table structure (i.e. field names and types) remains the same. The table name can be specified either explicitly, or using a project parameter.

Note that some databases don't have a native command for truncation. In such cases truncation is performed with the help of a DELETE query. The operation is atomic meaning that it either deletes everything, or, in case of a failure, nothing.

Custom command

Sends a free-form SQL query to the database, specified by connector.

The query can include parameters wrapped in braces. Parameters will be replaced with their values during runtime. For instance:

LOAD DATA INFILE '{FileName}' INTO TABLE {TableName};

The query can include multiple statements, if this is supported by the database connector.

Use cases

  • Export data into a database by instructing the database to bulk load a text file (e.g. previously generated by EasyMorph)
  • Trigger stored procedures

See also

transformations/sqlcommand.txt · Last modified: 2017/05/02 02:48 by dmitry