Converting CSV data to MERGE statements

R

Recently I have added a new functionality to the OPY project (you can read more about that in the Database versioning article). And that is the CSV data files conversion to MERGE statements.

I store some of the tables in the Git. Typically it is about LOV tables, list of users, roles, navigation... Basically all tables with data I need to run the application. I don't store any sensitive data this way.

I run this statement everytime I know I changed something or once a week:

python OPY/export.py -n PROJECT -r 0 -csv -v

And when I check my patch files, I can see for example the MERGE statement for the ROLES table:

--
-- database/data/roles.csv
--
MERGE INTO roles t
USING (
    SELECT 770 AS app_id, 'IS_ADMINISTRATOR'  AS role_id, 'Administrator' AS role_name, '' AS role_group, '' AS description_, 'Y' AS is_active, 1   AS order# FROM DUAL UNION ALL
    SELECT 780 AS app_id, 'IS_TESTER'         AS role_id, ''              AS role_name, '' AS role_group, '' AS description_, 'Y' AS is_active, ''  AS order# FROM DUAL
) s
ON (
    t.app_id 		= s.app_id
    AND t.role_id 	= s.role_id
)
--WHEN MATCHED THEN
--    UPDATE SET
--        t.role_name       = s.role_name,
--        t.role_group      = s.role_group,
--        t.description_    = s.description_,
--        t.is_active       = s.is_active,
--        t.order#          = s.order#
WHEN NOT MATCHED THEN
    INSERT (
        t.app_id,
        t.role_id,
        t.role_name,
        t.role_group,
        t.description_,
        t.is_active,
        t.order#
    )
    VALUES (
        s.app_id,
        s.role_id,
        s.role_name,
        s.role_group,
        s.description_,
        s.is_active,
        s.order#
    );
/

Typically I do only INSERTs, because I don't want to overwrite existing setup on a different environment, but the UPDATE option is there too, you just have to uncomment few lines.

If you are interested how it is done in Python, you can peak at the csv_to_merge function, and if you want to recreate it in a different language, you will need:

• list of all table columns (for insert part)
• list of columns used in primary key (to use in join condition)
• list of other columns (for update part)
• source data (you might want to convert DATE types and use DBMS_ASSERT for strings)
• fill the template:

MERGE INTO {table_name} t
USING (
    {csv_content_query}
) s
ON ({primary_cols_set})
WHEN MATCHED THEN
    UPDATE SET
        {non_primary_cols_set}
WHEN NOT MATCHED THEN
    INSERT (
        {all_cols}
    )
    VALUES (
        {all_values}
    );

You might also check the DML error handling article where I generate the MERGE in PL/SQL.