Copies one or more records identified by a SQL query from the designated source repository to the designated target repository. By default, all attributes are copied. Optionally, some attributes may be specified to be inhibited and others may be altered by specifying the attributes by name and their new value. The process returns the number of rows read from the query and the number of records successfully copied.
Inputs
- sql_query - Defines the SQL query to execute against the Enable database. The query may contain multiple result columns. The first column must be the InternalRecordId of the source repository record. If updating records, the second column must contain the InternalRecordId of the target record, otherwise it must be null. By requiring the target record's InternalRecordId to be specified gives maximum flexibility in how the source and target records are connected. If the target InternalRecordId is not defined (i.e., null), the record will be created (if the copy should only update existing records, the query must not return any rows where the second column is null. Any additional columns can be used to alter the values being overridden as defined in attrName and attrValue. NOTE: To use percent signs in the actual SQL query (e.g., wildcard in LIKE statement), precede each with a backslash character. For example: WHERE myColumn LIKE '\%something\%'. If referencing a work item property and the value may contain single quotes, surround the property reference with curly braces to ensure any single quotes are escaped. For example: WHERE myColumn LIKE '{%myProperty%}'. The curly braces must be inside the single quotes AND the property value cannot have any curly braces itself.
- sourceRepoName - The source repository name
- targetRepoName - The target repository name
- createRecord - Create a new record if 'true'. Otherwise, update an existing record as identified by the second result column returned by the query. If the second column is null, a new record will be created.
- enableTrigger - Activate trigger on target repository if true
- copyAttributes - Pipe-delimited list of attributes to be copied from the souurce record. If empty, include all attributes (except if any are defined in filterAttributes). If defined, filterAttributes is ignored.
- filterAttributes - Pipe-delimited list of attributes to be filtered out of the copy (i.e., not transferred from the source record. Only valid if copyAttributes not defined.
- validateAfterCopy - Validate the records after they have been copied if Yes (default is No)
- attrNameN - Name of attribute to be updated. If this attribute is transferred from the source record, it will be overridden by the corresponding value.
- attrValueN - Value for attribute to be updated. This may include any combination of literals, work item property references, or SQL result column references..
Outputs
- copyStatus - Results of copy operation (SUCCESS or FAIL)
- copyMessage - Detailed error message if copyStatus is FAIL
- sql_num_rows - Number of rows returned by the SQL query
- copy_num_rows - Number of rows copied