execsql-upsert

Fork

This repository contains ExecSQL scripts to perform automated upsert operations on any tables of any database in a DBMS that supports the standard information_schema views.

These scripts perform the upsert operation by using standard SQL UPDATE and INSERT statements rather than DBMS-specific implementations of the SQL MERGE statement.

Features of these upsert scripts include:

• They can be applied to any table in any database without modification.
• They can be applied to multiple tables simultaneously, and will perform the upsert operations in top-down order to maintain referential integrity among tables.
• Prior to performing the upsert operation, they check for null values in the columns of each staging table that must be non-null in the corresponding base table.
• Prior to performing the upsert operation, they check for duplicate primary key values in the staging tables.
• Prior to performing the upsert operation, they check foreign keys against both base tables and any other appropriate staging tables.
• They will not attempt to perform the upsert operation on any table if there are any violations of the non-null checks, primary key checks, or foreign key checks.
• They produce a table that either a) summarizes the number of rows that violated each type of non-null and foreign-key check, or b) summarizes the number of rows updated and the number of rows inserted for each table.
• Optionally, they will display all the changes to be made in a GUI interface, prompting the user to approve each update and insert operation.
• Optionally, they will record all operations carried out in a custom log file; this log may include the SQL statements executed and the data values that were added or changed.
• If an execsql console is active, they will use the console's status bar and progress bar to indicate the activity underway.

Implementations of these upsert scripts currently exist for PostgreSQL, MariaDB/MySQL, and MS-SQL Server.

Complete documentation is available at https://execsql-upsert.osdn.io/