MySQL insert multiple table at same time

Posted on 2009-02-22
Last Modified: 2012-05-06
Say, i want to insert data into 2 tables...table1 and table2 at the same time.
Any idea what's the SQL syntax to use?

- table1_id
- info1
- info2

- table2_id
- table1_id -> the id comes from table1
- info3
- info4
Question by:tangteng78
    LVL 142

    Accepted Solution

    you cannot insert "at the same time".
    you have to insert into table1, and then into table2:
    INSERT INTO table1 (info1, info2) VALUES('info1', 'info2');
    INSERT INTO table2 (table1_id, info3, info4) VALUES(last_insert_id(), 'info3', 'info4');

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the above is MySQL syntax. as you posted in both MySQL and MSSQL zones, please specify...

    mssql version:
    INSERT INTO table1 (info1, info2) VALUES('info1', 'info2');
    INSERT INTO table2 (table1_id, info3, info4) SELECT scope_identity(), 'info3', 'info4';

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now