Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.
After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package 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.
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.
In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Test data I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. UK1 […]
Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.
Using the MERGE statement, you can insert into, delete from and update the same table all at once: that is the magic. If you don’t pay attention, you can also make the database do a lot of unnecessary work: that is the madness! I’ve blogged a lot about comparing tables, then using MERGE to synchronize them. […]
Happy New Year to all those kind enough to read my blog! 2014 was the year I started blogging and presenting at Oracle-related conferences. It has been a great experience. I enjoy studying specific problems in depth and being able to correct or improve my efforts without constraints. Thanks very much to all those who […]
I’m on my way to UKOUG Tech14, where I will present Database 12c MATCH_RECOGNIZE: Beating the Best Pre-12c Solutions on Tuesday at 3 P.M., room 4B. With all the Oaktable ACE Directors presenting at the same time, I don’t expect a big crowd but I’ll do my best for those who do turn up. This is my […]
When you load data with an “effective date” into a Data Warehouse, what happens when the new data is the same as yesterday’s? Do you add a new record with the new “effective date” or do you update a date range in the existing record? At Open World last month, I got both answers: Tim Gorman […]