Batch insert SQL statement

Question

I have say 100 rows data to insert in MySQL database table.
But i dont want to write all 100 INSERT statements.
Is there any bulk insert Statement in SQL ??? Please help with code if possible.

eggyal · Accepted Answer · 2012-05-03 10:17:54Z

3

As the MySQL manual states:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Furthermore, from the section on speed of INSERT:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.3, “Server System Variables”.

answered May 3, 2012 at 10:17

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

Comments

zerkms · Accepted Answer · 2012-05-03 10:25:08Z

2

INSERT INTO tbl (col1, col2) VALUES ('val1', 'val2'), ('val3', 'val4'), ...

And please read documentation first next time

edited May 3, 2012 at 10:25

answered May 3, 2012 at 10:17

zerkms

256k73 gold badges447 silver badges551 bronze badges

2 Comments

Sai Kalyan Kumar Akshinthala Over a year ago

Sorry, actually I dint deleted it. I just modified the query as code parts.

zerkms Over a year ago

@Sai Kalyan Akshinthala: ah, got it. We made 2 edits in the same time, and yours was first, sorry

Collectives™ on Stack Overflow

Batch insert SQL statement

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related