Thread: Insert Into

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    2
    Rep Power
    0

    Unhappy 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. ....??
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    4) sounds very much like instead of triggers so I would not call it a unique concept, or is there any difference?
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Hi rycamor, straight from an Oracle whitepaper:
    Multi-table inserts offers the benefits of the INSERT . . . SELECT statement
    when multiple tables are involved as targets.
    .....

    INSERT FIRST
    WHEN cust_credit_limit >=4500 THEN
    INTO customers_special VALUES(cust_id,cust_credit_limit)
    INTO customers
    ELSE
    INTO customers
    SELECT * FROM customers_new;
    I've found it very useful in datawarehousing.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    2
    Rep Power
    0

    Lightbulb


    Thank you all...back to the drawing board

IMN logo majestic logo threadwatch logo seochat tools logo