CODEWITHSUNDEEP
mysqlsql-insert
JErne
JErne

Reputation: 151

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?

Upvotes: 13

Views: 73258

Answers (3)

JErne
JErne

Reputation: 151

Just an update on how I did do this. Since I don't have full access to the database server, I can just add/remove data and create new tables, it was not possible to create a function as suggested in the link provided in the answer. Instead of trying to loop through the data I did an INSERT for each new row like:

INSERT INTO table2 (id,col2,col3)
SELECT id,'name',name FROM table1;
INSERT INTO table2 (id,col2,col3)
SELECT id,'email',email FROM table1;

Thanks again for the help provided.

Upvotes: -1

abinash sahoo
abinash sahoo

Reputation: 177

Just do a simple INSERT INTO SELECT with group by "id". Here for each id it will insert a new record.

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

Upvotes: 1

Obsidian Age
Obsidian Age

Reputation: 42304

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 http://dev.mysql.com/doc/refman/5.7/en/insert-select.html and http://www.w3schools.com/sql/sql_insert_into_select.asp.

EDIT:

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 :)

Upvotes: 35

Related Questions