MySQL Executing multiple INSERTs in 1 query

Question

Hey guys I was wondering is it possible to use more than 1 INSERT at a time per query? Or does each INSERT have to be done one at a time?

For example I would like to execute these 2 queries as one to save on server resources:

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('2012-09-01', '5', '4', '3', '2') ON DUPLICATE KEY UPDATE supportStaff = '5', startEmails = '4', endEmails = '3', emailsAnswered = '2'
INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('2012-09-2', '5', '6', '7', '8') ON DUPLICATE KEY UPDATE supportStaff = '5', startEmails = '6', endEmails = '7', emailsAnswered = '8'

Is there anyway of combining them? I tried just sticking them in the same query but it didn't like it.

make a stored procedure and call it.

Abubakkar
– Abubakkar

2012-10-10 10:01:08 +00:00
Commented Oct 10, 2012 at 10:01 — Abubakkar
– Abubakkar, Commented Oct 10, 2012 at 10:01

AnandPhadke · Accepted Answer · 2012-10-10 10:08:00Z

1

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) 
VALUES ('2012-09-01', '5', '4', '3', '2') 
ON DUPLICATE KEY UPDATE supportStaff = '5', startEmails = '4', endEmails = '3', emailsAnswered = '2';

('2012-09-2', '5', '6', '7', '8') 
ON DUPLICATE KEY UPDATE supportStaff = '5', startEmails = '6', endEmails = '7', emailsAnswered = '8'

answered Oct 10, 2012 at 10:08

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

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

Comments

alan978 · Accepted Answer · 2012-10-10 10:19:49Z

1

Yes you can try this

INSERT INTO tbl_name(col1,col2,col3) VALUES ('aaa','bbb','ccc'),('ddd','eee','fff');

or you can put semicolon after first query and continue with second one.

Hope this helps.

edited Oct 10, 2012 at 10:19

answered Oct 10, 2012 at 10:05

alan978

5553 silver badges6 bronze badges

Comments

Yogesh Suthar · Accepted Answer · 2012-10-10 10:08:28Z

0

seprate by semicolon ";" like :

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) 
VALUES ('2012-09-01', '5', '4', '3', '2') ON DUPLICATE KEY UPDATE supportStaff = '5', 
startEmails = '4', endEmails = '3', emailsAnswered = '2';

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) VALUES 
('2012-09-2', '5', '6', '7', '8') ON DUPLICATE KEY UPDATE supportStaff = '5', startEmails = 
'6', endEmails = '7', emailsAnswered = '8';

edited Oct 10, 2012 at 10:08

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

answered Oct 10, 2012 at 10:01

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL Executing multiple INSERTs in 1 query

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