This syntax automatically triggers a COMPARE in order to make the target table, cursor, rows and key columns match the source.

Syntax


SYNC [/*+ … */] target TO source [WITH KEY (col1,...)]

Arguments

  • /*+ … */ - Insert optional SYNC-TO Cirro Hint.

  • source - source datasource can be a table or query.

  • target - target datasource must be a table.

  • 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.

Additional Information

  • The number of affected rows is returned as the updated count.

  • Hints are always optional.

    • FORCE_SORT ({TRUE | FALSE}) - Use the sorted or unsorted compare engine.

    • FORCE_TRANSFERTHREADS - The number of transfer threads of execution that will be used to move data from place to place in that part of the query structure. FORCE_TRANSFERTHREADS will cause the compare engine to use N difference loggers.

    • If FORCE_ROWCOUNT or FORCE_FETCHSIZE need to be different on a second table, rewrite the table as follows:


SELECT /*+ hints */ * FROM table

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)

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) ;

See Also