You know, sometimes I’m amazed at how much there is to know about the Oracle database…typically when I discover something which I feel I should have known about a long time ago, but which, for some reason, I’m only discovering now. My latest surprise is the INSERT ALL command, which was added to the database back in Oracle 9i.
Put simply, INSERT ALL allows you to add data to multiple tables at the same time. By itself, this is obviously of limited utility, but you can put different columns into different tables (so, if table A has columns 1, 2, and 3, while table B has columns 1, 4, and 5, both can be handled in the same statement. And, to make things better, you can use conditions to control which table the data goes into–so, if you had a table for credits and a separate table for debits (finance people ask for the weirdest things, sometimes), you can use one statement to filter your data appropriately:
insert all when amt > 0 then into credits_tbl(...) values(...) else into debits_tbl(...) values(...) select .... from transactions_tbl where....
There’s also a (related) INSERT FIRST command, which uses the same syntax; the only difference is that it stops after the first true condition, while INSERT ALL evaluates all of them (also note: order is not guaranteed!)
When I first encountered this, I was hopeful that it would be useful for normalizing large chunks of data quickly, but I haven’t been able to figure out a way to do it–everything I’ve tried results in duplicate data being added to the parent table. But even without that functionality (which I would love), this remains a powerful feature which I’m glad I’ve finally found.