MS SQL Table Update

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.

Properties

Connection

Type: Connection Input
The Connection Property

Server
Type: String
The SQL Instance

Database
Type: String
The database which is used

UserId
Type: String
Username

Password
Type: Password
Password

UseWinAuth
Type: Boolean
If the DropPoint run as a user, this option can be used instead of UserId and Password

TableXml

Type: Xml Input
The TableXml Property

KeyFields

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.

RowChunkSize

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)

AllowInsert

Type: Boolean Input
Indicates whether inserts are allowed into the target table.

AllowUpdate

Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).

RowsAffected

Type: Int32 Output
Returns the number of rows affected by the insert/update operation.

Remarks

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.

See Also

Sql Table Update