Mysql Insert Into Select (Copy data from another table/database table)

Hello, has long seemed to me not to write again. But now I can write about copy data from another table or from table on another database.
First we must create database and the table:

mysql> create database learn;
mysql> use learn;
mysql> create table guest_book1( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50), tlp VARCHAR(15));
mysql> create table guest_book2( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50), tlp VARCHAR(15));
mysql> INSERT INTO guest_book1(name, tlp) VALUES('user 1', '089123456789'), ('user 2', '089223456789'), ('user 3', '089323456789'), ('user 4', '089423456789'), ('user 5', '089523456789');


Example I am want to copy data from table “guest_book1” to table “guest_book2”. If we are using PHP, so to copy that data may be we will do something like this:

<?php
$conn = new PDO('mysql:host=localhost;dbname=learn', "root", "password");

// Get data from table 1
$sql = "SELECT * FROM guest_book1";
$db = $conn->prepare($sql);
$db->execute();
$result = $db->fetchAll(PDO::FETCH_OBJ);

// Insert data/copy data to another table
$sql2 = "INSERT INTO guest_book2(name, tlp) VALUES(:name, :tlp)";
$db2 = $conn->prepare($sql2);
for($i = 0; $i < count($result); $i++) {
	$db2->bindValue(':name', $result[$i]->name, PDO::PARAM_STR);
	$db2->bindValue(':tlp', $result[$i]->tlp, PDO::PARAM_STR);
	$db2->execute();
}

// Get data from table 2
$sql3 = "SELECT * FROM guest_book2";
$db3 = $conn->prepare($sql3);
$db3->execute();
$result2 = $db3->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
echo "Table 1";
print_r($result);
echo "Table 2";
print_r($result2);
echo "</pre>";
?>

But this way is a waste. And the simple way is using “mysql insert into select function”. So the code is to be like this:

mysql> INSERT INTO guest_book2(name, tlp) SELECT name, tlp FROM guest_book1;

And now after run that code, we can get result/data from table guest_book1 is same with table guest_book2:

mysql> SELECT * FROM guest_book1;
+----+--------+--------------+
| id | name   | tlp          |
+----+--------+--------------+
|  1 | user 1 | 089123456789 |
|  2 | user 2 | 089223456789 |
|  3 | user 3 | 089323456789 |
|  4 | user 4 | 089423456789 |
|  5 | user 5 | 089523456789 |
+----+--------+--------------+
5 rows in set (0,00 sec)

mysql> SELECT * FROM guest_book2;
+----+--------+--------------+
| id | name   | tlp          |
+----+--------+--------------+
|  1 | user 1 | 089123456789 |
|  2 | user 2 | 089223456789 |
|  3 | user 3 | 089323456789 |
|  4 | user 4 | 089423456789 |
|  5 | user 5 | 089523456789 |
+----+--------+--------------+
5 rows in set (0,00 sec)

But the question is how if the table is from different database?
So for the answer, example I have structure database like this:

— Database 1 name –> learn
     — Table –> guest_book1
— Database 2 name –> learn2
     — Table –> guest_book2

So if we want copy data from table guest_book1 in learn database to guest_book2 from learn2. The code is:

mysql> INSERT INTO learn2.guest_book2(learn2.guest_book2.name, learn2.guest_book2.tlp) SELECT learn.guest_book1.name, learn.guest_book1.tlp FROM learn.guest_book1;
Query OK, 5 rows affected (0,06 sec)
Records: 5  Duplicates: 0  Warnings: 0

And that is the magic 😀
Source: http://www.w3schools.com/sql/sql_insert_into_select.asp

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s