Db app

 -- Insert new records into master table

INSERT INTO $master_table (

    parent,

    parent_sys_class_name,

    parent_u_cmdb_ci_used_for,

    parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,

    parent_ref_cmdb_ci_service_u_cmdb_ci_ser,

    modified_date

)

SELECT

    c.parent,

    c.parent_sys_class_name,

    c.parent_u_cmdb_ci_used_for,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,

    c.modified_date

FROM $child_table c

LEFT JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser

WHERE m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser IS NULL;


-- Log inserted rows

SPOOL $inserted_rows_file

SELECT

    c.parent,

    c.parent_sys_class_name,

    c.parent_u_cmdb_ci_used_for,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,

    c.modified_date

FROM $child_table c

LEFT JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser

WHERE m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser IS NULL;

SPOOL OFF;


-- Update modified records in master table

MERGE INTO $master_table m

USING $child_table c

ON (m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser)

WHEN MATCHED THEN

UPDATE SET

    m.parent = c.parent,

    m.parent_sys_class_name = c.parent_sys_class_name,

    m.parent_u_cmdb_ci_used_for = c.parent_u_cmdb_ci_used_for,

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code = c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,

    m.modified_date = SYSDATE

WHERE

    m.parent != c.parent OR

    m.parent_sys_class_name != c.parent_sys_class_name OR

    m.parent_u_cmdb_ci_used_for != c.parent_u_cmdb_ci_used_for OR

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code != c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code OR

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser != c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser;


-- Log updated rows

SPOOL $updated_rows_file

SELECT

    c.parent,

    c.parent_sys_class_name,

    c.parent_u_cmdb_ci_used_for,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,

    c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,

    c.modified_date

FROM $child_table c

JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser

WHERE

    m.parent != c.parent OR

    m.parent_sys_class_name != c.parent_sys_class_name OR

    m.parent_u_cmdb_ci_used_for != c.parent_u_cmdb_ci_used_for OR

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code != c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code OR

    m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser != c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser;

SPOOL OFF;

Comments

Popular posts from this blog

SQL Loader V3

Db Unix

Standby redo log add