SQLServer Insert

Use this activity to execute an SQL Insert to insert the records into the Microsoft SQL Server database.

Settings

The Settings tab has the following fields.

Field Description
Connection Name of the Microsoft SQL Server database connection from which to retrieve information. You can select a connection from the drop-down list.

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 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 on 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 on 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.
Manually Configure Metadata If you see an error for a valid SQL statement in the Query field, set the Manually Configure Metadata field to True to fetch table metadata manually using simple SELECT statement. 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 will be presented in the Values node and substitutions which do not match will 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.

Related concepts
Related reference