9

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?

AZinkey
  • 5,209
  • 5
  • 28
  • 46
Ashley
  • 141
  • 1
  • 7
  • 3
    Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) – Tom Udding Nov 21 '17 at 10:59
  • seems your query is fine, what error you get on above query – AZinkey Nov 21 '17 at 11:08
  • The query is correct . Try running the query in db directly.If insert happens properly then something wrong with php code . Also check if transactions are committed in db if not you can’t see the data . – kartik Nov 21 '17 at 11:15
  • @kartik could you be more clearify on transactions are committed .? – Ashley Nov 21 '17 at 11:18
  • @Ashley Try https://stackoverflow.com/questions/47411332/select-data-from-db-table-1-and-insert-it-into-another-db-table-2-in-php/47411571#47411571 – helpdoc Nov 21 '17 at 11:21
  • @Ashley what I meant is , after any DML statements we need to commit . We need to run “commit” command . – kartik Nov 21 '17 at 11:22
  • Thanks all of you .. – Ashley Nov 21 '17 at 13:06

4 Answers4

7

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

AZinkey
  • 5,209
  • 5
  • 28
  • 46
  • Okay @AZinkey it does makes sense.. now the problem is its not updating the new entries . Thanks – Ashley Nov 21 '17 at 11:28
  • new entries means the user input.. Like what ever the user posts it will go first to db.Table1 and then goes to db.Table2 .. – Ashley Nov 21 '17 at 11:39
  • idk why its not working, Can you see this $sqlfin= "CREATE TRIGGER copy_records AFTER INSERT ON `dbtest`.`Table1` FOR EACH ROW FOLLOWS BEGIN INSERT INTO `dbtest2`.`Table2` END"; I also change the trigger_order to PRECEDES .. – Ashley Nov 21 '17 at 12:09
  • 1
    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 – AZinkey Nov 21 '17 at 12:34
  • Zinkey Okay now its working.. Thanks for following me all the way up to here .. Cheers!! – Ashley Nov 21 '17 at 13:05
1

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;
helpdoc
  • 1,910
  • 14
  • 36
1

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)

AymDev
  • 6,626
  • 4
  • 29
  • 52
0
INSERT INTO dbtest2 ( 
      id, 
      name, 
      status ) 
SELECT id, 
      name,          
       '1'
FROM dbtest
ORDER BY id ASC

You can use INSERT...SELECT syntax. Note that you can quote '1' directly in the SELECT part.

Nims Patel
  • 1,048
  • 9
  • 19