| 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 insert 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 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, input fields in the
Input tab of the activity are also populated based on the SQL Insert statement.
Note: Be sure to include the semicolon (;) at the end of the 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.
Note: When substitution variables used in the VALUES clause are identical to the column names in the table, those variables are displayed under the
Values array in
Input tab.
The following example represents a typical insert query:
INSERT INTO ADVISOR (s_id, i_id) VALUES (?s_id, ?i_id);
For the above insert query, there will not be any output field, and input field is generated for
s_id (VARCHAR) and
i_id(VARCHAR) under Values[] node as its part of the values clause. Also, the mapped value for the field price and name is substituted into the substitution variable
?s_id and
?i_id.
It is also possible to enter multi-row queries. For example:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99)
Value substitution variables can also be used in multi-row values. For example:
INSERT INTO products VALUES (1, 'Cheese', ?price), (2, 'Bread', ?price), (3, 'Milk', ?price)
If multi-row values are used, it is important to 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 ouput 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.
- For multiple record insertion, mixing of value substitution variables and parameter substitution variables in each record entry is not recommended. Either they must all be value substitution variables or they must be all parameter substitution variables. They should not be a combination of both. For example, the following use is discouraged:
INSERT INTO products (ProductNo, Name, Price)
VALUES (?ProductNo, ?Name, ?Price), (?myproduct, ?myname, ?myprice);
|
