SQL to copy data from source to target.

Syntax

DATACOPY /*+ … */ source TO target
[WITH
  [DROP [IF EXISTS]]
  [CREATE [IF NOT EXISTS]]
  [{TRUNCATE | DELETE}]]
  [APPEND]]
  [[NO]CONSTRAINTS] [[NO]INDEXES] [[NO]TRIGGERS]]
[<IGNORE> <TRANSACTION>]

Arguments

  • /*+ … */ - Insert optional Cirro Hint

    • FORCE_* hints are added to the SELECT for either INSERT and CTAS.

    • SOURCE_HINT text is added to INSERT, but not to CTAS (unsupported).

    • FORCE_ROWCOUNT(N) - N will be the expected source and target result.

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

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

  • WITH

    • DROP - if the target exists, DROP and CREATE before inserting.

    • CREATE - if the target does not exist, create it as CTAS.

    • TRUNCATE - truncate the target table before inserting. Raise error if table does not exist.

    • APPEND - only insert. Raise error if table does not exist.

    • [NO]CONSTRAINTS - disable any target table constraints before inserting. Raise error if table does not exist.

  • [NO]INDEXES - drop any target table indexes before inserting. Re-create afterwards.

  • [NO]TRIGGERS - disable any target table triggers before inserting. Raise error if table does not exist.

  • [ ] - This option allows us to skip using a temporary table for movements and inserts directly into the destination table. This will result in faster loads, but partial data may be loaded in the event of an error.

Additional information

  • All columns must be named - Cirro’s underlying code requires that all columns are named. It will not use generated column names.

For example:

DATACOPY (SELECT MAX(id) FROM lmy.inventory.inventory.datamove_data) TO lora.ORCLPDB1.CIRRO.datamove_create WITH DROP IF EXISTS

This statement throws an error

Error: [Code: -1, SQL State: 42909] The CREATE TABLE statement does not include a column list.
  • The primary key of an IOT is not affected by the NOCONSTRAINTS option.

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)

This default options are:

  • WITH CREATE IF NOT EXISTS - If the target does not exist, it will be created.

  • APPEND NOCONSTRAINTS TRIGGERS - If it does exist, constraints will be disabled but triggers will not be before the source records are appended to the existing records in target.

DATACOPY lim.imdb.public.aka_name TO lms.imdb.dbo.aka_name ;

This always does a drop and create. It will fail if the target does not exist.

DATACOPY lim.imdb.public.aka_name TO lms.imdb.dbo.aka_name WITH DROP ;

This always does a drop if the target exists and then creates the target.

DATACOPY lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword WITH DROP IF EXISTS ;

Incremental data copy

DATACOPY (SELECT * FROM lim.imdb.public.aka_name WHERE id BETWEEN 1000000 AND 2000000)
      TO lms.imdb.dbo.aka_name
    WITH APPEND NOCONSTRAINTS NOINDEXES;