Dump values from a table to another one in MySQL

Question

Say I have a table called dogs that have the following columns:

id, fk_hospital, fk_owner, date, name

And I want to create another table that will have the following columns:

fk_hospital, fk_owner, fk_dogs, what_type

In this second table fk_dogs will be the id of the first table. And what_type by default will be NULL.

So basically, I want to dump from the first table to the second table. The create table of the second table looks like this:

CREATE TABLE dogs_info (
fk_hospital char(4) NOT NULL,
fk_owner char(11) NOT NUL,
fk_dogs int(11) unsigned NOT NULL,
what_type tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY(fk_hospital, fk_owner, fk_dogs)
)

How could I dump the content from the first to the second? Do I need any server language, like PHP? Or I can do it straight with mysql?

EDIT: I want to do this by chunks, so first do the dump for a particular fk_hospital and fk_owner, and then for next fk_owner. Can this be done with MySQL too?

Check http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from — Osiris, Nov 21 '12 at 09:20

score 3 · Accepted Answer · answered Nov 21 '12 at 09:21

3

You can use MySQL:

INSERT INTO dogs_info SELECT fk_hospital, fk_owner, id, NULL FROM dogs

answered Nov 21 '12 at 09:21

Jens

2,050
1
14
30

Perfect. I have edited my question because I would like to this with chunks, because the primary table is really big. – Hommer Smith Nov 21 '12 at 09:27
I think in this case you need multiple querys. You could generate your querys with MySQL and then execute them. – Jens Nov 21 '12 at 09:29

score 0 · Answer 2 · answered Nov 21 '12 at 09:21

0

use INSERT INTO..SELECT statement

INSERT INTO dogs_info(k_hospital, fk_owner, fk_dogs)
SELECT fk_hospital, fk_owner, ID as fk_dogs
FROM dogs

answered Nov 21 '12 at 09:21

John Woo

258,903
69
498
492

Olaf Dietsche · Answer 3 · 2012-11-21T09:36:05.607

You can try this select and while loop

$result = $mysqli->query('select fk_hospital, fk_owner from dogs');
while ($row = $result->fetch_assoc()) {
    $mysqli->query('insert into dogs_info ' .
                   '(fk_hospital, fk_owner, fk_dogs) ' .
                   'select fk_hospital, fk_owner, id ' .
                   'from dogs ' .
                   'where fk_hospital = ' . $row['fk_hospital'] .
                   ' and ' . $row['fk_owner']);
}

Mudassir Hasan · Answer 4 · 2012-11-21T11:38:59.467

0

No need of PHP .

INSERT INTO dogs_info(fk_hospital, fk_owner, fk_dogs)
SELECT fk_hospital,fk_owner,id
FROM dogs

See INSERT INTO ... SELECT

For inserting in chunks use ORDER BY clause

 INSERT INTO dogs_info(fk_hospital, fk_owner, fk_dogs)
    SELECT fk_hospital,fk_owner,id
    FROM dogs
    ORDER BY fk_hospital,fk_owner

See ORDER BY

edited Nov 21 '12 at 11:38

answered Nov 21 '12 at 09:25

Mudassir Hasan

28,083
20
99
133

Dump values from a table to another one in MySQL

4 Answers4