We all know this: if a long-bearded prophet came down from a mountaintop bearing the ten commandments of Oracle programming, one of them might read thus:
Thou canst select from many tables, but thou may only update, delete from or insert into one table at a time.
Right? Well, not exactly. Because you can actually add data to multiple tables with a single insert statement.
In my experience, even though multi-table inserts have been possible in Oracle since 9i, it is a rarely-used feature outside the circles of ETL geeks ( and no, I do not want you to send me emails telling me how everyone you know – from your granddad to that homeless guy down your street – uses multi-table inserts all the time.)
Multi-table inserts are a means by which we can obtain data from a single source, and propagate it to multiple targets.
Advantages of Multi-table Inserts
And here are some reasons why you might want to use this feature a little more regularly.
- There’s a Tom Kyte dictum: never use multiple statements to do something you can achieve in one. (I think it’s Tom Kyte I heard say that; it might have been some other wise man.) The one obvious advantage of multi-table inserts is that with a single statement you can carry out a process that otherwise would take you numerous statements.
- A second, less obvious, advantage follows on from that: with a multi-table insert your code is more efficient, seeing as it involves just a single round trip to the server, rather than numerous trips.
- Thirdly, once you’re comfortable with the syntax (which we’ll discuss next), multi-table inserts are easy to read and easy – tidier – to write.
Multi-table inserts do suffer from some restrictions, too; however, we’ll get to them later. Let’s take a look at the syntax first.
Unconditional Multi-table Inserts
There are actually two types of multi-table insert statements: conditional and unconditional. Let’s look at the latter first, we’ll return to conditional multi-insert statements a little later.
There are a few things that I’d like you to notice:
- The ALL keyword is mandatory with unconditional multi-table insert statements.
- You can have as many pairs of the insert-into clause and values clause as you require. (If you had only one it wouldn’t be a multi-table insert, would it?)
- The source subquery is mandatory.
An example would probably be useful at this juncture, right?
INSERT ALL INTO table1 (col1, col2, col3) values (source_table.col1, source_table.col2, source_table.col3) INTO table2 (col1, col2, col4) values (source_table.col1, source_table.col2, source_table.col4) INTO table3(col1, col5, col6) values (source_table.col1, source_table.col5, source_table.col6) SELECT * FROM source_table;
The above statement will take the relevant columns from source_table and insert them into table1, table2 and table3. Without multi-table inserts, this would have taken three separate insert statements.
And that’s that for unconditional multi-table inserts; nice and straightforward.
Oh, by the way, if you’re finding that requirement for a source subquery a little restrictive, here’s a nice little trick for you: select from dual.
INSERT ALL INTO table1 (col1, col2, col3) values (1, 'Mexico', sysdate + 1) INTO table2 (col1, col2, col4) values (2, 'Tonga', sysdate + 2) INTO table3 (col1, col4, col6) values (3, 'Norway', sysdate + 3) SELECT * FROM dual;
Conditional Multi-table Inserts
Conditional multi-table inserts are cleverer than their unconditional cousins and, at first glance, look a lot more complicated. Trust me, they aren’t. Just imagine an Insert statement and a Case statement got drunk together and, nine months later, had a baby; it’d probably look a lot like a conditional multi-table insert. Here’s the syntax:
Let’s dive straight in with an example. Imagine we created 3 new tables – Top_earners, mid_earners and low_earners – and we wanted to populate them from the standard EMP table, based on which tax bracket each member of staff falls into.
INSERT ALL WHEN (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal < 3000 AND sal >= 1500) THEN INTO mid_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal < 1500) THEN INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) SELECT * FROM emp;
Taking another look at our statement, I realise that we can tweak it a little, using the ELSE clause.
INSERT ALL WHEN (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal < 3000 AND sal >= 1500) THEN INTO mid_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) ELSE INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) SELECT * FROM emp;
The final piece of the jigsaw puzzle that we need to talk about is the FIRST keyword that can be used in conjunction with conditional multi-table insert statements (but not with unconditional multi-table inserts).
If you begin your statement with INSERT FIRST instead of INSERT ALL, Oracle will analyse each WHEN condition (in order, from the top) and once it has found a condition that resolves to TRUE it will carry out that insert and not analyse any of the subsequent WHEN conditions. It will do this for each row returned by the source subquery.
Is that clear? No? I didn’t think so. Let me see if I can make it a little clearer by rewriting our EMP example using INSERT FIRST. Remember that we want to segregate our employees based on how much money they earn.
INSERT FIRST WHEN (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal >= 1500) THEN INTO mid_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal > 0 ) THEN INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) SELECT * FROM emp;
The above statement would have made no sense with an INSERT ALL. King, whose salary is 5000, would have ended up being inserted into all three tables (since 5000 is greater than 3000, and 5000 is greater than 1500, and 5000 is greater than nothing). However, with INSERT FIRST, Oracle executes the insert statement associated with the first condition that is true, and ignores all subsequent ones. So when we get to Blake, who earns 2850, the top condition will resolve to false and be ignored, the second condition will resolve to true and so his details will be inserted into the mid_earners table; and even though the last condition would have resolved to true too, the database does not even glance at it.
I did say that there were a few restrictions that you’d have to bear in mind, didn’t I? Fortunately, they’re nothing major. However:
- You should not use sequences in multi-table insert statements.
- You cannot use a multi-table insert to write to a remote table.
- You can only use multi-table inserts with tables. Not views, materialized or otherwise.
- The sum of all the columns in the various insert-into clauses of a multi-table insert statement must not exceed 999.
- You cannot use a TABLE collection expression in a multi-table insert statement.
That’s about it, really. In my personal, day-to-day, work experience I find that conditional multi-table inserts are a little more useful than the vanilla unconditional multi-table inserts. However, I have to confess that even I often forget that they exist and I fall back to using the reliable – but less efficient – PL/SQL.