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 Create Table As Select.

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

if say CONSTRAINTS, overrides default

Additional information

  • All columns must be named. DATACOPY 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
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:

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

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

DATACOPY with DROP then CREATE. Statement fails if target does not exist.

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

DATACOPY with DROP if target exists followed by CREATE.

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

Incremental DATACOPY appending NOCONSTRAINTS and NOINDEXES

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