11

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?

Sumit patel
  • 3,807
  • 9
  • 34
  • 61
JErne
  • 133
  • 1
  • 1
  • 4
  • Can show your attempt?? – Rupsingh Aug 17 '16 at 08:38
  • I tried something like this: INSERT INTO table2 VALUES (ID, 'name ', name), VALUES (ID, 'e-mail', e-mail), VALUES (ID, 'phone ', phone) SELECT * FROM table1; And got that there is an invalid SQL statement... – JErne Aug 17 '16 at 09:27

3 Answers3

28

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

Community
  • 1
  • 1
Obsidian Age
  • 41,205
  • 10
  • 48
  • 71
  • Not really, I want the result from each returned row in table 1 to be transformed into several rows in table 2 where each row contains a subset of the result from table 1. – JErne Aug 17 '16 at 08:36
  • Thanks for the feedback! I've got a better understanding of your task at hand now, and have updated my answer accordingly. Hopefully it's more helpful now :) – Obsidian Age Aug 17 '16 at 10:07
  • Thanks! That looks like a working solution! The reason I need to do this is that I have a website that we are moving to Wordpress. In the old site we had all users like in Table1, but Wordpress with the add-ons we need to use has most of the user information as i Table2. I could not find any migration tool to help me, hence I need to do this in the database. – JErne Aug 17 '16 at 10:15
1

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;
Impulse The Fox
  • 2,638
  • 2
  • 27
  • 52
abinash sahoo
  • 167
  • 1
  • 5
0

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.

Mohammad Kholghi
  • 533
  • 2
  • 7
  • 21
JErne
  • 133
  • 1
  • 1
  • 4