I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables.

insert into tab(id,name,date) values
((select max(id)+1 from tab),(select name from tab1),now());

Can you tell me how to do achieve this?

Whether this can be achieved by simple query itself or stored procedure is the only option?

  • 1
    How many rows does tab1 have? If it has, say 100 rows, do you want 100 rows to be inserted into tab? It would also be good if you provided the CREATE TABLE scripts for both tables involved. – ypercubeᵀᴹ Sep 7 '15 at 10:46

This is typically achieved by writing a SELECT statement that outputs the fields in the same order as the INSERT statement and using that instead of a VALUES clause. For example, one might:

INSERT INTO my_table (field1, field2)
SELECT t1.field1, max(t2.field2)
FROM t1 INNER JOIN t2 ON (...)
GROUP BY t1.field1
WHERE something_else = 'whatever';

The point is that you can just do an


to insert rows. You do not have to create a VALUES clause and mess with subqueries.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.