Provides an efficient way for executing bulk insert/update on a database without having to hand-write INSERT or UPDATE statements. Executes statements in batches unlike the Sql Table Update node.
Type: Connection Input
The SQL Instance
The database which is used
If the DropPoint run as a user, this option can be used instead of UserId and Password
The Connection Property
Type: Xml Input
The TableXml Property
Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml property. It is not necessary to supply key fields if AllowUpdate is false.
Type: Int32 Input
The maximum amount of rows to be processed in each statement. This will be overridden if the amount of parameters is going to exceed the maximum allowed by SQL (2100)
Type: Boolean Input
Indicates whether inserts are allowed into the target table.
Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).
Type: Int32 Output
Returns the number of rows affected by the insert/update operation.
The XML supplied should contain the following paths root\tablename\columnname. The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.
When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.
For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.
When a column is an identity column, and specific values needs to be inserted, a custom field 'IdentityInsert' with value 'true' could be added to the node.