Insert multiple rows into a table with a single statement

Question

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?

Ed Bangga · Accepted Answer · 2022-08-17 01:45:21Z

1

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

answered Aug 17, 2022 at 1:45

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diamaudix Audio Ltd. Over a year ago

Thanks!!! Works like a charm. "tag_id" is in a separate table, but was able to modify the code to INSERT INTO sample_tag (sample_id, tag_id) SELECT sample_id, "1" from sample where sample_id<=500

Collectives™ on Stack Overflow

Insert multiple rows into a table with a single statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related