SQLServer Insert
Use this activity to execute an SQL Insert to insert the records into the Microsoft SQL Server database.
Input Settings
The Input Settings tab has the following fields:
| Field | Description |
|---|---|
| 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 is substituted into the substitution variable at run time. If the query contains parameters then only parameter metadata is populated. End the query with semicolon (;) to fetch the metadata in the table at the bottom of the query. 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 and otherwise displayed under Parameters on the Input tab. Use Values to insert multiple records at once and use Parameters to insert only one record at a time. Map JSON data from previous activity for insert when you use:
For more details about Data Mappings, see "Mapping JSON Data with the json.path() Function" section in TIBCO Flogo Enterprise User's Guide. 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. |
| Manually Configure Metadata | Set the Manually Configure Metadata field to True to fetch table metadata manually. For more information, see Manually Configuring Metadata. |
| Fields | The grid is provided for informational purpose only. |
Input
This tab displays the input schema of the activity in a tree structure format. The information in the schema varies based on the insert query provided. Substitution parameters in the query, which exactly match column names, are presented in the Values node and substitutions, which do not match, appear in the Parameters node.
Output
This tab displays the output schema of the activity in a tree structure format. The output tab displays rowsAffected and lastInsertId. rowsAffected equals the number of rows inserted and lastInsertId will be set to the id of the last server generated unique key value if the table is defined that way, otherwise -1.