Automatic Column Key Assignment for User Defined Compareset Column Mappings

In version 15.7.1 SP202, SAP DA can assign key columns automatically for a compareset with user-defined column mappings.

Use the new with map option to map columns in a compareset when there are no key columns.

Note: You can set the with map option only for the strict_key parameter in the create compareset or replace compareset command.

When there are no key columns assigned in the compareset, SAP DA searches for any of these:

Primary key columns. If a primary key is found, SAP DA sets it as a key.
Identity columns. If an identity column is found, SAP DA sets it as a key.
Unique index columns. If a uniquely indexed column is found, SAP DA sets it as a key.

If no primary key, identity or unique index columns are found and when the:

strict_key parameter is false (default setting), SAP DA uses all columns as the key.
strict_key parameter is true, SAP DA will not create the compareset and the “no key found” error message is displayed.

Set the strict_key parameter using an optional with map option as:

[with map option set strict_key [{to|=}] {true|false}]

For example, if you have a source and target table with these columns:

create table person (
  first_name varchar(50),
  last_name varchar(50),
  dob datetime,
 primary key(last_name, first_name)
)

You can create a compareset without the set key=true column option:

create compareset person
with source conn1 dbo person s
     target conn2 dbo person t
map s.first_name = t.first_name
and s.last_name = t.last_name
and s.dob = t.dob
with map option set strict_key = true
go

SAP DA will assign the column keys:

show compareset person columns
go
TYPE CONNECTION OWNER TABLE  MAPID COLUMN     KEY
---- ---------- ----- ------ ----- ---------- ----
S    conn1      dbo   person 1     first_name 2
T    conn2      dbo   person 1     first_name 2
S    conn1      dbo   person 2     last_name  1
T    conn2      dbo   person 2     last_name  1
S    conn1      dbo   person 3     dob
T    conn2      dbo   person 3     dob