Search code examples
sqldatabaseinsert

Insert multiple rows into a table with a single statement

Looking to insert 500 rows into many-to-many table (sample_tag).

Aiming to have 500 new records created in sample_tag linking the first 500 rows from sample to associated tag_id in the tag table.

The following code seems to make sense, but triggers an error because the nested SELECT statement returns more than one row:

INSERT INTO sample_tag (sample_id, tag_id) 
VALUES ((SELECT sample_id FROM sample WHERE sample_id <= 500), 1)

What could be the correct SQL to accomplish this insert for multiple rows?

Solution

  • You can have multiple inserts by not using VALUES keyword. You need to specify same number of columns on your source table.

    INSERT INTO sample_tag (sample_id, tag_id) 
SELECT sample_id, tag_id from sample where sample_id<=500