Differences

This shows you the differences between two versions of the page.

--- transformations:sqlcommand [2016/06/19 18:44] – created dmitry
+++ transformations:sqlcommand [2017/05/01 22:48] – dmitry
@@ Line 1: / Line 1: @@
 ===== SQL Command =====
-Sends a free-form SQL query to a database, specified by a [[:connectors|connector]].
+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 [[:parameters|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 [[:parameters|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 [[:parameters|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 [[:connectors|connector]].
 The query can include [[:parameters]] wrapped in braces. Parameters will be replaced with their values during runtime. For instance:
-  TRUNCATE TABLE "{TableName}"
+  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 load a text file (e.g. previously generated by EasyMorph)
+  * Export data into a database by instructing the database to bulk load a text file (e.g. previously generated by EasyMorph)
-  * Truncate/drop/create tables
   * Trigger stored procedures
 ** See also **
-  * [[http://easymorph.com/learn/export-data.html|Tutorial: Exporting data]]
+  * [[http://easymorph.com/learn/export-database.html|Tutorial: Exporting data into a databse]]