• R/O
  • SSH

execsql-upsert: Repository summary

Default repository for the execsql-upsert scripts


Recent Commits RSS

Rev. Time Author Message
8c636a69de57 2020-05-10 01:33:04 Dreas Nielsen tip Modified progress bar display for better performance.
98d0a593abe0 2020-04-29 09:38:28 Dreas Nielsen Modified to explicitly roll back changes if do_commit is ...
2da9348ffd67 2020-01-06 10:22:52 Dreas Nielsen Modified documentation per change from Bitbucket/RTD to O...
9a6099ac5dad 2019-08-02 00:16:10 Dreas Nielsen Minor edits to documentation.
2c93c8d3caef 2019-06-26 11:09:10 Dreas Nielsen Updated version number in the change log.
1baa96727b3a 2019-06-24 09:26:27 Dreas Nielsen Updated documentation to describe UPDTPK_ONE.
ef2f84bc2569 2019-06-24 05:13:40 Dreas Nielsen PK UPSERT script completed for MariaDB/MySQL. All versio...
5d867f6a5a68 2019-06-04 11:33:08 Elizabeth Shea Added UPDTPKQA_ONE to pg_upsert and correction to logging...
462eeeeb09f0 2019-05-30 10:55:08 Elizabeth Shea Added script UPDTPK_ONE to pg_upsert
8714d454fe71 2019-05-29 10:37:19 Elizabeth Shea Corrected ups_cols selection in UPSERT_ONE script within ...

Recently edited Tags

Name Rev. Time Author
tip 8c636a69de57 2020-05-10 01:33:04 Dreas Nielsen

Branches

Name Rev. Time Author Message
default 8c636a69de57 2020-05-10 01:33:04 Dreas Nielsen Modified progress bar displ...

README.rst

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/

Show on old repository browser