Here is a summary of the posts about my SQL-based methods for comparing and synchronizing tables:
- Compare tables: Tom Kyte and GROUP BY - Compare tables, views or queries (with or without primary keys) using GROUP BY.
- Generating SQL with SQL templates - Use "SQL templates" and queries against the data dictionary to generate SQL code.
- Compare and sync tables: GROUP BY and MERGE - Compare tables using GROUP BY, then sync to the target using MERGE (target has primary key).
- Compare and sync tables: Generating the code - Use an "SQL template" to generate code to synchronize tables (with primary keys).
- MERGE magic and madness - Use the MERGE statement to do "upserts". If you want to synchronize tables without doing DELETEs, MERGE will do the job without a prior "compare" step.
- MERGE using Change Data Capture (CDC) - Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.
- Sync tables: generate MERGE using Unique constraint - How to generate SQL to compare and sync tables, when the target table has a UNIQUE constraint instead of a primary key. Warning: doesn't work if some rows have NULL values in all the columns used by the constraint.
- Compare and Sync without Primary Keys - I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.
- COMPARE_SYNC: Introducing the package - This helper tool generates SQL for
- Comparing tables, views and queries, both local and remote.
- Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.
Pingback: More Pitfalls of the MINUS comparison | Clean Database Development