Insert Into

Is there a way to use the INSERT INTO statement in SQL for more than one table? For example if you are normalizing a database and find that the data in one table needs to be placed in two tables. ....??

There is no such thing as an INSERT statement on two tables, but there are various ways of accomplishing your aims, depending on the database system involved.

1. Updateable views. In some database systems you can define a view, with two or more joined tables, and INSERTS or UPDATES into the view can propagate to the sub-tables. This is an iffy proposition, though. There are all kinds of quirks and limitations on view updateability, depending on which system you use.

2. More commonly this would be the job of a trigger, in most modern database systems. (If you are talking about MySQL, then you are out of luck. Actually, MySQL doesn't support any of the methods we discuss here). If you are not familiar with triggers, just think of them as an action that is "pre-set", to happen upon insert or update to a particular table. This pre-set action can be an SQL query or procedural function which carries out any operation you want, such as inserting duplicate data to another table.

3. Stored procedures. Triggers are usually set to call stored procedures. But, you can call a stored procedure directly, without a trigger. This stored procedure can easily insert the parameters you pass to several tables. Some database developers insist that ALL access to tables occur through stored procedures, so that the front-end application developer never even uses one SQL query. This gives the database admin complete flexibility to change internal schema at will, while preserving the external interface.

4. (A unique concept) Query rewrite rules, which is a concept that only exists in my favorite system: PostgreSQL. This is similar to a trigger, but aimed simply at redirecting the action of an SQL query to carry out a completely different query, or an additional query, or a series of queries, using the data from the "input" query. Think of it as being analogous to mod_rewrite for Apache, and you have the idea: anything can be something other than it seems on the outside.

If the database system you are using doesn't support any of these concepts, then you must handle the problem in your front-end application code.

4) sounds very much like instead of triggers so I would not call it a unique concept, or is there any difference?

As a sidenote, Oracle 9i and maybe other databases allow some kind of multitable inserts.

swampboogie -

The RULE concept is very similar to triggers, except it uses a different mechanism internally, and (I believe) has less of a performance hit than a trigger. One difference is that it can be defined for any access to a table or view, including a SELECT. (AFAIK) Many DMBS systems only define triggers for INSERT, DELETE, and UPDATE. This makes it a great way to log all access to a particular table or view, for example. Also, it is the mechanism used to make views updateable, or even insertable, in PostgreSQL.

I guess, to illustrate better, a RULE can take a query that is illegal, such as one that inserts the wrong number of columns into a table, and turn it into a legal one, by inserting those extra columns in another table, or whatever you want. It can be a way to keep the external access to a table, while changing the internals of the table completely. Thus, it allows you to practice a small amount of black magic .

pabloj - I'm curious; what is the syntax for a multitable INSERT, in Oracle.

Really, for all of this, a simple answer to vicncat's question, which even works for MySQL, is to have two insert queries wrapped in a transaction, to preserve the integrity of the operation. Thus if one fails, they both fail.

Hi rycamor, straight from an Oracle whitepaper:


I've found it very useful in datawarehousing.

Thank you all...back to the drawing board