| Insert
|
An 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 query statement.
Each substitution variable identifies an input parameter whose mapped value will be substituted into the substitution variable at runtime. You can reuse the substitution variable for the same input parameter elsewhere in the insert query. The type information for the input parameters used in the VALUES and RETURNING clause is fetched from the database using the selected connection for the entered insert query. Also based on the output parameters used in the RETURNING clause, the corresponding type information is fetched from the database using the selected connection for the entered insert query. Similarly, input and output fields in the
Input and
Output tabs of the activity are also populated based on the SQL Insert statement.
Note: Be sure to include the semicolon (;) at the end of a query. This activity expects an insert query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging.
The following examples represent insert queries:
-
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', ?price), (2, 'Juice', ?price), (3, 'Milk', ?price ) returning (select name from instructor where name = ?name);
For the above insert query, output field is generated for
name and
input field is generated for
price (NUMERIC) under Values[] node as its part of values clause, and name(VARCHAR) under parameters node as it is part of the parameter select sub-query . Also, the mapped value for the field price and name is substituted into the substitution variable
?price and
?name.
-
INSERT INTO products (product_no, name, price) VALUES (?product_no, ?name, ?price) returning price;
For the above insert query, output field is generated for
price and
input fields are generated for
product_no (INTEGER),
name(TEXT), and
price(NUMERIC). Also, the mapped value for the field
product_no,
name,
price is substituted into the substitution variables
?product_no,
?name,
?price. The parameters node in the input tab will not have mappings as there is no parameter in the insert query statement.
|
