How to insert records in loop in MySQL based on results from another table

Question

I have a TableA from where I would like some information for e.g. tableA has columns **name, age and rollNo** and I would like to insert **age** and **rollNo** from tableA to tableB wherever the age is greater than 20 and rollNo less than 500

Is there anyway this can be done in MySQL using a procedure or something.

"Insert records in a loop" is always a bad idea. This can be done in a single statement. — Cᴏʀʏ
– Cᴏʀʏ, Commented Sep 17, 2015 at 21:13
@Cᴏʀʏ sometimes it is unavoidable. In such cases, a stored procedure making use of CURSORs is the only way to go if it must be done on the server. — Uueerdo
– Uueerdo, Commented Sep 17, 2015 at 21:26
@Uueerdo: I guess I don't know how powerful MySQL's query syntax is -- I have never used a CURSOR in T-SQL, for example (instead, WHILE loops are fantastic). You can almost always write a loop without a CURSOR, even if CURSOR is the easy way out. Stuff like that should be a last resort (in my humble opinion, of course). — Cᴏʀʏ
– Cᴏʀʏ, Commented Sep 17, 2015 at 22:04
@Cᴏʀʏ You still need to loop through cursor results in MySQL stored procs. It's not an instead thing; just what you use to be able to iterate through results within a stored procedure. — Uueerdo
– Uueerdo, Commented Sep 17, 2015 at 22:31

Cᴏʀʏ · Accepted Answer · 2015-09-17 21:14:35Z

2

This can be done in a single query using the INSERT ... SELECT syntax:

INSERT INTO TableB (Age, RollNo)
SELECT Age, RollNo
FROM TableA
WHERE Age > 20 AND RollNo < 500

answered Sep 17, 2015 at 21:14

Cᴏʀʏ

108k20 gold badges169 silver badges201 bronze badges

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

1 Comment

john.p.doe Over a year ago

@Cory I had to make a few modifications but worked like a charm. Thanks a lot.

Collectives™ on Stack Overflow

How to insert records in loop in MySQL based on results from another table

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