Select data from db table 1 and insert it into another db table 2 in php

Question

I have two different Databases, names:

1. dbtest: Table 1
2. dbtest2: Table 2

I want to select all the data and new entries from dbtest Table 1 to dbtest2 Table 2.

I have tried this

$sqlfin = "INSERT INTO dbtest2.Table2 SELECT * FROM dbtest.Table1";
$resultfi = mysqli_query($db_conn, $sqlfin);

But no luck so far. How can I assure that new Records are insert into both table ? Any help would be appreciated?

Answer 1

lets try it in this format

INSERT INTO `dbtest2`.`Table2` SELECT * FROM `dbtest`.`Table1`

The following conditions hold for INSERT ... SELECT statements:

• Specify IGNORE to ignore rows that would cause duplicate-key violations.
• AUTO_INCREMENT columns work as usual.
• To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT statements (see Section 8.11.3, “Concurrent Inserts”).
• To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

INSERT ... SELECT Syntax

---

Create Trigger: for adding new entries

CREATE TRIGGER copy_record BEFORE INSERT ON dbtest.Table1 
FOR EACH ROW 
BEGIN 
   INSERT INTO dbtest2.Table2 (first_name, last_name) VALUES (new.first_name, new.last_name); 
END

trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:

INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE

statements.

UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE

and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.

CREATE TRIGGER Syntax

Answer 2

Try this query for your desired task :

Query First (Create Table exactly same like in old database, if you have not):

CREATE TABLE dbtest2.Table2 LIKE dbtest.Table1;

Query Second (Insert all data to newly created table) :

INSERT INTO dbtest2.Table2 SELECT * FROM dbtest.Table1;

Answer 3

Your query looks correct but will fail if the 2 tables have a different structure. Specify the columns to avoid it like:

INSERT INTO dbtest2.Table2 (2_col_1, 2_col_2) SELECT 1_col_1, 1_col_2 FROM dbtest.Table1

---

With PDO (kind of alternative answer, I don't know much for Mysqli):
You could connect to Mysql with PDO without giving a database name when working with multiple ones (but this isn't mandatory), like:

$db = new PDO( "mysql:host=" . $host . ";", $user, $password, $options );

Then write the Database names, tables and columns like when making a JOIN (as you did): separated by a .

// an example ..
$useDb = $db->query("INSERT INTO db_1.table1 (value_1, value_2) SELECT value_3, value_4 FROM db_2.table2 WHERE db_2.table2.id = 5");

(example tested and working fine)