How To Insert Multiple Rows with One INSERT Statement

Q

How To Insert Multiple Rows with One INSERT Statement? - MySQL FAQs - Understanding SQL INSERT, UPDATE and DELETE Statements

✍: FYIcenter.com

A

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives a good example:

mysql> INSERT INTO fyi_links SELECT id+500, REVERSE(url), 
   notes, counts, created FROM fyi_links;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 101 | dev.fyicenter.com | NULL  |      0 | 2006-04-30    |
| 102 | dba.fyicenter.com | NULL  |      0 | 2006-08-31    |
| 103 | sqa.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 110 |                   | NULL  |   NULL | 2006-08-31    |
|   0 | www.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 601 | moc.retneciyf.ved | NULL  |      0 | 2006-04-30    |
| 602 | moc.retneciyf.abd | NULL  |      0 | 2006-08-31    |
| 603 | moc.retneciyf.aqs | NULL  |   NULL | 2006-08-31    |
| 610 |                   | NULL  |   NULL | 2006-08-31    |
| 500 | moc.retneciyf.www | NULL  |   NULL | 2006-08-31    |
+-----+-------------------+-------+--------+---------------+
10 rows in set (0.00 sec)

2007-05-11, 4701👍, 0💬