Two SYNC statements to sync data between source and target.

Match rows on key columns of the target

This command performs the actions on the target table that are required to make it match the source table or cursor, matching rows on the key columns of the target.

Syntax


SYNC target TO source [WITH KEY (col1,...)]

Arguments

  • source - source datasource in form system.schema.database

  • target - target datasource in form system.schema.database

  • WITH KEY

    • The sync actions are performed on the target table matching the source table or cursor.

    • Rows are matched on the Key columns of the target.

    • If the KEY is not given, SYNC searches for the table’s primary key.

Match rows saved in a log table

This command performs the actions on the target table that have been saved in the log table.

Syntax

SYNC target [FROM {LOG | target_clog} [WITH KEY (col1,...)] [USING 'compare_name']]

Arguments

  • target - Target database.

  • LOG - the log table containing actions for the target table.

  • target_clog - target_clog defaults are same as the COMPARE command. Whether the target_clog table is temporary or permanent, the SYNC is constructed from:

    • Delete from target

    • Update target

    • Insert into target

These are based on the target_clog action column and joining on the key.

  • WITH KEY

    • If the key is not given and the target is a table, the primary key of the target is used.

    • Must be unique to avoid unexpected results.

  • USING ‘compare_name’ - if omitted all records in the log table will be used for the SYNC.

Additional Information

  • For both forms, the number of affected rows is returned as the updated count.

Unique KEY

Table A:

ID Name
1 house

Table B:

ID Name
1 house
1 car

If these tables are compared with id as the key, the first records with name house are matched.

The second record in B does not match any record in A, so the compare will flag id = 1 to be deleted.

If that action is taken, the matching record will also be deleted.

In this case, the appropriate key is (id, name).

If the given compare key is not unique, the results of the compare may be unexpected.

Examples

Example tables are drawn from the imdb database, for instance:

  • cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id)

  • movie_keyword (id, movie_id, keyword_id)

  • company_name(id, name, country_code, imdb_id, name_pcode_nf, name_pcode_sf)

SYNC target TO source [WITH KEY { (col1[,…]) | (*) }]

The target has a primary key.

SYNC lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword ;

Use all the columns as the comparison key.

SYNC lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword WITH KEY (*) ;

Compare based on a non-primary key.

SYNC lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword WITH KEY (keyword, phonetic_code) ;

SYNC target [FROM {LOG | target_clog} [WITH KEY { (col1[,…]) | (*) }] [USING ‘…’]]

SYNC using results of a previous COMPARE that were saved in the default table, lim.imdb.public.keyword_clog.

The key is included because the primary key has not been defined on the target table.

SYNC lim.imdb.public.keyword FROM LOG WITH KEY (id) ;

SYNC using results of a previous COMPARE that were saved in the named table, and with the same primary key defined on the target.

SYNC lim.imdb.public.keyword FROM lms.cirrodb.dbo.keyword_clog ;

SYNC using results of a previous COMPARE that were saved in the default table with a specific name.

SYNC lim.imdb.public.keyword FROM LOG AS 'Post-run check #1' ;