Inserting multiple rows into a table is simple.

INSERT INTO table1 (id) VALUES (1), (2)

But what if you want to use a subquery to replace the explicit query with something more dynamic. This should be possible, however, searching the internet didn’t get me (or my colleagues) any closer to figuring this out. The obvious attempt of replacing (1), (2) with (subquery) resulted in the error your subquery returns multiple records

In the end we discovered that removing VALUES from the query gave the desired effect.

INSERT INTO table1 (id) (SELECT id FROM table2)

Executing the above query will insert a row into table1 for every result returned by the subquery.

Related posts:

  1. Tips for MySQL to PostgreSQL Switch  If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get...
  2. Swap Insert for Update When Key Exists  When synchronizing tables or databases, sometimes it is useful to have a way to update a row if there is...
  3. How to Break a MySQL Left Join  Care must be taken when placing conditions on the results of the right-hand table of a LEFT JOIN because it...