Insert into Multiple Tables in One Query

MySQL Insert into multiple tables? (Database normalization?)

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

Have a look at LAST_INSERT_ID() to reuse autoincrement values.

You said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

Let me elaborate: there are 3 possible ways here:

1. In the code you see above. This
   does it all in MySQL, and the
   LAST_INSERT_ID() in the second
   statement will automatically be the
   value of the autoincrement-column
   that was inserted in the first
   statement.

   Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID() will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

2. Will stock the LAST_INSERT_ID() in
   a MySQL variable:

   INSERT ...
   SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
   INSERT INTO table2 (@mysql_variable_here, ...);
   INSERT INTO table3 (@mysql_variable_here, ...);
   

3. Will stock the LAST_INSERT_ID() in a
   php variable (or any language that
   can connect to a database, of your
   choice):

   • INSERT ...
   • Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you
   • INSERT [use your php variable here]

WARNING

Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries (for example, your database-server crashes). If you can live with "some have finished, others not", don't read on.

If however, you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. That's why I used the BEGIN and COMMIT here.

How to INSERT into multiple tables from one SELECT statement

You can use the OUTPUT clause to insert into the second table. If you want to insert into more than two tables, you'd have to use some other method.

Sample data

DECLARE @MainTable TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T1 TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T2 TABLE (Name nvarchar(50), Address nvarchar(50));

INSERT INTO @MainTable (Name, Address) VALUES
('Name1', 'Address1'),
('Name2', 'Address2'),
('Name3', 'Address3');

Query

INSERT INTO @T1 (Name, Address)
OUTPUT inserted.Name, inserted.Address INTO @T2 (Name, Address)
SELECT Name, Address
FROM @MainTable
;

Result

SELECT * FROM @T1;
SELECT * FROM @T2;


+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

Execution plan

Data insertion into multiple tables with one query using postgres

postgreSql has different syntax for that. I was stuck in this also a few days back figured out how to deal with it. You can user BEGIN block to insert data into multiple tables for that you please follow this query and your work will be done.

BEGIN;
                WITH userins AS (
                INSERT INTO public.user(
                ""FirstName"", ""LastName"", ""Email"", ""PasswordHash"",""VerificationCode"", ""Phone"", ""IsEnabled"", ""IsVerified"", ""IsDeleted"",  ""CreatedDate"")VALUES(@FirstName, @LastName, @Email, @PasswordHash, @VerificationCode, @Phone, @IsEnabled, @IsVerified, @IsDeleted, @CreatedDate)
                    RETURNING id AS user_id)
                ,clientins AS(
                INSERT INTO public.client(""Description"", ""PlanCustomerId"", ""IsActive"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", UserId) 
                        VALUES(@Description, @PlanCustomerId, @IsActive, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT user_id from userins)) RETURNING id as client_id)
                ,clientsubins AS
                (
                   INSERT INTO public.client_subscription(""PlanId"", ""IsActive"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId, SubscriptionId)
                   VALUES(@PaymentMethodPlanId, @IsActive, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id from clientins), @PlanId)
                )
                ,clientpurchaseins AS
                (
                   INSERT INTO public.client_purchase_history(""PlanId"", ""InvoiceId"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId)
                   VALUES(@PlanId, @InvoiceId, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id FROM clientins))
                )
                SELECT client_id from clientins;
        COMMIT;

Here is Begin block will take care of your transaction if there is any error occurred then it will be rollback automatically.

How to insert data in multiple tables using single query in SQL Server?

To me - this seems a lot cleaner, and it also will be a lot simpler to understand (and maintain!) in the future:

-- check to see if your data already exists
IF NOT EXISTS (SELECT * 
               FROM search_results 
               WHERE company_id = 4 AND link = 'https://test.com')
BEGIN TRY
  BEGIN TRANSACTION
    -- if not -> insert into the first table
    INSERT INTO search_results (company_id, link, title, domain)   
    VALUES (4, 'https://test.com', 'title', 'test.com');

    -- grab the last identity value from that previous INSERT       
    DECLARE @LastId INT;
    
    SELECT @LastId = SCOPE_IDENTITY();
    
    -- insert into the second table
    INSERT INTO corporate_statements (statement_link_id, corporate_statement) 
    VALUES (@LastId, 1);

    COMMIT;
END TRY
BEGIN CATCH
    -- in case of an error rollback the full transaction
    ROLLBACK;
END CATCH;

and you're done. Or am I missing something? I think this would be doing what you're described in the intro of your post - not necessarily what you're showing in your code...

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

Insert into multiple tables in one query

MySQL doesn't support multi-table insertion in a single INSERT statement. Oracle is the only one I'm aware of that does, oddly...

INSERT INTO NAMES VALUES(...)
INSERT INTO PHONES VALUES(...)

Inserting a select result into multiple tables

No, it's not possible. You need to issue two separate insert statements.

INSERT INTO table1(v1, v2)
SELECT v1, v2, v3, v4 FROM old_table;

INSERT INTO table2(v3, v4)
SELECT v1, v2, v3, v4 FROM old_table;

You could wrap this up in a transaction.

However, if you have more sophisticated need and that might be inserting the same data into multiple tables which you don't want to type by hand you could write a loop inside a procedure and execute dynamic statements providing table and column names. INFORMATION_SCHEMA tables would be a good place to start.

---

Related Topics