JErne JErne - 1 year ago 115
MySQL Question

SQL Query to INSERT multiple rows with SELECT

I need to read data from one table and insert into multiple rows in another table in a MySQL database.

Table 1 looks like:

ID, name, e-mail, phone, city, ..., ....

In Table 2 I need to insert data like:

(row1) ID, "name", name
(row2) ID, "e-mail, e-mail
(row3) ID, "phone", phone

Table 1 has about 3000 rows

I guess I need to use some kind of foreach or do..while but can't find anything that works.
Can anyone give me a clue how to do this?

Answer Source

If I understand your question correctly, you are wanting to do a query on table1 that returns multiple rows, and then insert those into table2 in a single loop. That's the INSERT INTO SELECT statement:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1;

It can be modified to grab specific results as well:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1
     WHERE name = 'target person';

More information can be found at and


Based on your comment, it sounds like you're trying to do this: SQL split values to multiple rows.

I can't think of a situation where you'd actually want to do that, as you can access all of the data in your existing table as is, and it seems to be bad practice to split data in the way you're requesting. However, the solutions in the above thread should be applicable to what you're trying to do.

Ultimately, you may want to look at how you're actually retrieving the data. Modifying that code would be a better idea :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download