replace compareset

Syntax

replace compareset compareset_name
[set desc [{to|=}] description]
with source source_connection_name  owner_name 
source_table_name source_alias [where “constraint”]
target target_connection_name owner_name 
target_table_name target_alias [where “constraint”]
[and target target_connection_name owner_name 
target_table_name target_alias [where “constraint”]...] 
map {all [set strict_column [{to|=}] {true|false}] 
[and set strict_key [{to|=}] {true|false}]
[and set strict_type [{to|=}] {true|false}] 
[and set keep_computed [{to|=}] {true|false}]
[and set keep_encrypted [{to|=}] {true|false}]|
source_table_alias.column_name = target_table_alias.column_name
[ = target_table_alias.column_name ...] 
[set key=true ], 
[and source_table_alias.column_name = 
target_table_alias.column_name [ = 
target_table_alias.column_name ...] 
[set key=true]…] }

Parameters

• compareset_name – the name of the compareset to be replaced.

• description – (optional) description of the agent. Use double quotes if you are using a reserved word or blank spaces.
• source_connection_name – the name of the source connection.
• target_connection_name – the name of the target connection.
• owner_name – the name of the owner for the source or target tables.
• source_table_name – the name of the source table.
• target_table_name – the name of the target table.
• source_alias – the alias that refers to source connection, owner, and table.
• target_alias – the alias that refers to target connection, owner, and table.
• column_name – the name of the column.
• key – specifies whether the column is a primary key or an unique key.
  Note: When you use the map all statement, the compareset key columns are set by examining the primary key, identity and uniquely indexed columns in the source database table.
• strict_column – if set to true, this parameter throws an exception if one or more source columns do not exist in target tables. By default, strict_column is false.
• strict_key – if set to true, this parameter checks the source database table for at least one primary key, identity or uniquely indexed column. If no primary key or a uniquely indexed column is found, the command fails and the compareset is not created. If set to false, this parameter uses all non-LOB columns as compareset key columns. By default, strict_key is false.
  Note: When strict_key is false and all non-LOB columns are used as the compareset key columns, the keys might not be unique. Non-unique keys are detected during the data comparison and this might terminate the data comparsion prematurely.
• strict_type – if set to true, this parameter checks the type name, scale, and precision. By default, strict_type is false.
  Note: The strict_type, strict_column, strict_key, keep_computed, and keep_encrypted are used only when you use the map all statement, which requires the source and target databases to be online; while for explicit mappings, databases need not be online.

Examples

• Example 1 – replaces the current “cust1” compareset with a new compareset definition:

  replace compareset cust1 
  with source prod1 dbo_cust1 s 
       target prod1 dbo_cust1 t
  map all  
  go

Usage