Snowflake Insert

SQL statement used to insert a record in the table. You can construct prepared SQL queries by using substitution variables or parameters of the form ?<fieldname> in the insert query statement.

Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at runtime. You can reuse the substitution variable for the same input parameter elsewhere in the query. The type information for the input parameters used in the VALUES clause is fetched from the database using the selected connection for the entered insert query. Similarly, the input fields on the Input tab of the activity are also populated based on the SQL Insert statement. You must include a semicolon (;) at the end of the query. To indicate the end of the insert query, a semicolon is expected at the end. A missing semicolon causes the query to be unresponsive.

Example of an insert query:

INSERT INTO employee (name, salary) VALUES ('test1', ?salary);

INSERT INTO products (product_no, name, price) VALUES (?product_no, ?name, ?price);

For the above insert query, input fields are generated for product_no (INTEGER), name (TEXT), and price (NUMERIC). The mapped value for the field product_no, name, price is substituted into the ?product_no, ?name, and ?price substitution variables. The parameters node in the Input tab do not have mappings as there is no parameter in the insert query statement.

It is also possible to enter multi-row queries. For example:

INSERT INTO student (roll_no, name, marks) VALUES (1,'Student1', 99), (2, 'Student2', 80), (3, 'Student3', 75); 

 Value substitution variables can also be used in multi-row values. For example:

INSERT INTO products VALUES (1,'Student1', ?marks), (2, 'Student2', ?marks), (3, 'Student3', ?marks); 

 If multi-row values are used, note the following points:

• A value substitution variable must appear in all the rows at the same position. You cannot have some rows with the value variable and other rows without it.
• If the value row is mapped to the output values from previous activities, then the output data from the previous activity must contain the same number of records as there are rows entered in the insert query definition.