Looping over a SQL result with multiple rows in MySQL

Question

Let's say I have a "source table" which holds some rows, in this case four. Now I'd like to insert new rows into a "target table" for each of the rows from the source table.

My current statement is:

SET @id = 1;
INSERT INTO target_table (id, value)
VALUES (@id, 1),
       (@id, 2),
       (@id, 3),
       (@id, 4);

However I'd like to do something like this...

SET @id = 1;
myResultSet = SELECT value FROM source_table;
FOR EACH value in myResultSet
    INSERT INTO target_table (@id, @value)
END

Why do you want to have all id values as 1 in your target_table ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 11, 2018 at 9:39
It's the ID of a "fixed employee" who can have multiple permissions. — Robert Strauch
– Robert Strauch, Commented Oct 11, 2018 at 9:42

Madhur Bhaiya · Accepted Answer · 2018-10-11 09:50:03Z

1

You can use Insert ... Select statement:

INSERT INTO `target_table` (`id`, `value`) 
SELECT 1, `value` 
FROM `source_table`

Completely possibilities of this statement can be seen from the syntax below:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

edited Oct 11, 2018 at 9:50

answered Oct 11, 2018 at 9:38

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

Comments

LoztInSpace · Accepted Answer · 2018-10-11 09:40:12Z

1

INSERT INTO target_table ( id, value )
SELECT  row_number() OVER (order by <whatever>) , value FROM source_table

or

INSERT INTO target_table( id, value )
SELECT  1, value FROM source_table

depending on what you mean

answered Oct 11, 2018 at 9:40

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Comments

Irfan Bhindawala · Accepted Answer · 2018-10-11 10:01:58Z

1

INSERT INTO target_table( id, value )
SELECT  1, value FROM source_table

answered Oct 11, 2018 at 10:01

Irfan Bhindawala

3331 silver badge7 bronze badges

Collectives™ on Stack Overflow

Looping over a SQL result with multiple rows in MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related