Oracle Generate Merge Script

I have found this script and its so useful, I had to share it again.

Anytime you want to sync your data from the production database to the test database you can use this script.

JUST BE VERY CAREFUL TO NOT UPDATE YOUR PRODUCTION DATA WITH TEST DATA.

I like to run it on the test database, to be safe I create a DB link with a user that just have only Read Only access on the production data, to ensure you don’t update production data.

If you have more than one test database its always advisable to test it first between test databases.

This script wont update any data it will only generate the merge statement that you can use to update your data.

RazorFink Table Merge


--------------------------------------------------------------------------------
--Meta SQL for scaffolding table merge statements
--  Usage:
--    Execute SQL
--    export/copy lines to sql file
--    make necessary edits
--  TODO:
--    make all column references explicit
--------------------------------------------------------------------------------

WITH
    target AS
    (SELECT
        upper(trim(:owner))      AS owner,
        upper(trim(:table_name)) AS table_name
      FROM
        dual
    ),
    all_cols AS
    (SELECT
        atc.owner       AS owner,
        atc.table_name  AS table_name,
        atc.column_name AS column_name,
        atc.column_id   AS column_id,
        atc.data_type   AS data_type,
        atc.data_length AS data_length
      FROM
        all_tab_cols atc,
        target
      WHERE
        atc.owner           = target.owner      AND
        atc.table_name      = target.table_name AND
        atc.hidden_column  != 'YES'             AND
        atc.virtual_column != 'YES'
      ORDER BY
        column_id
    ),
    pk_cols AS
    (SELECT
        ac.owner           AS owner,
        ac.table_name      AS table_name,
        acc.column_name    AS column_name,
        acc.position       AS position,
        all_cols.column_id AS column_id
      FROM
        all_constraints ac,
        all_cons_columns acc,
        all_cols
      WHERE
        ac.owner            = all_cols.owner AND
        ac.table_name       = all_cols.table_name AND
        ac.constraint_type  = 'P' AND
        acc.owner           = ac.owner AND
        acc.table_name      = ac.table_name AND
        acc.constraint_name = ac.constraint_name AND
        acc.column_name     = all_cols.column_name
      ORDER BY
        acc.position
    ),
    data_cols AS
    (SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        all_cols
    MINUS
    SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        pk_cols
      ORDER BY
        column_id
    ),
    sql_parts AS
    (SELECT
        'SELECT'                 AS sql_select,
        'FROM'                   AS sql_from,
        'WHERE'                  AS sql_where,
        'ORDER BY'               AS sql_order_by,
        ';'                      AS sql_semi,
        'MERGE INTO'             AS sql_merge_into,
        'USING'                  AS sql_using,
        'ON'                     AS sql_on,
        'WHEN MATCHED THEN'      AS sql_when_matched,
        'UPDATE SET'             AS sql_update_set,
        'WHEN NOT MATCHED THEN'  AS sql_when_not_matched,
        'INSERT'                 AS sql_insert,
        'VALUES'                 AS sql_values,
        '('                      AS sql_paren_open,
        ')'                      AS sql_paren_close,
        chr(10)                  AS sql_lf,
        '  '                     AS sql_t,
        MAX(LENGTH(column_name)) AS max_col_length
      FROM
        dual,
        all_cols
    )
  --------------------------------------------------------------------------------
  SELECT
      STATEMENT
    FROM
      (SELECT
          1              AS statement_order,
          rownum         AS row_order,
          sql_merge_into AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          2      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||lower(owner)
          ||'.'
          ||lower(table_name)
          ||' o' AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          3         AS statement_order,
          rownum    AS row_order,
          sql_using AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          4      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          5      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_select AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          6      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || rpad(lower(all_cols.column_name), sql_parts.max_col_length, ' ')
          ||rpad(' AS '
          ||all_cols.column_name
          ||(
            CASE
              WHEN lead(all_cols.column_id) over (order by all_cols.column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END), sql_parts.max_col_length+6, ' ')
          ||'-- '
          ||all_cols.data_type
          ||sql_parts.sql_paren_open
          ||all_cols.data_length
          ||sql_parts.sql_paren_close AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          7      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_from
        FROM
          sql_parts
      UNION
      SELECT
          8      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          || lower(target.owner
          ||'.'
          ||target.table_name) AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          9      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || sql_order_by AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          10       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || lower(column_name)
          || (
            CASE
              WHEN lead(position) over (order by position) IS NOT NULL
              THEN ','
              ELSE NULL
            END )
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          11     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close
          ||' n' AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          12     AS statement_order,
          rownum AS row_order,
          sql_on AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          13     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          14       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by position) IS NOT NULL
              THEN ' AND'
              ELSE NULL
            END) AS STATEMENT
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          15     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          16               AS statement_order,
          rownum           AS row_order,
          sql_when_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          17     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_update_set AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          18     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          data_cols,
          sql_parts
      UNION
      SELECT
          19                   AS statement_order,
          rownum               AS row_order,
          sql_when_not_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          20     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_insert AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          21     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          22     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'o.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          23     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          24     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_values
        FROM
          sql_parts
      UNION
      SELECT
          25     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          26     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          27     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          28       AS statement_order,
          rownum   AS row_order,
          sql_semi AS STATEMENT
        FROM
          sql_parts
      )
    ORDER BY
      statement_order,
      row_order ;

Leave a Reply

Your email address will not be published. Required fields are marked *


CAPTCHA Image
Reload Image