如何使用其他数据库表中的一个select插入数据

Question

I have two databases and i have one table "TabelaX" in database "Servidor1" with out data and other database "Servidor2" with one table "TabelaY". And i want do one select in table "TabelaY" and with her data do one insert in table "TabelaX" which is in another database. I already made some code but it is not working correctly. And this error appears:

"Error: INSERT INTO TabelaX (ID, Nome, Dados) VALUES (2000, XPTO2, 12345); Unknown column 'XPTO2' in 'field list'Error: INSERT INTO TabelaX (ID, Nome, Dados) VALUES (2033, XPTO3, 1234567890); Unknown column 'XPTO3' in 'field list'"

<?php



      $conn= mysqli_connect('localhost','root',null,'Servidor2') or die 
       (mysqli_connect_error());

if (!$conn) {
    die("Falha de conexao: ". mysqli_connect_error());

}

 $ID = $_POST['ID'];
 $sql = "SELECT * FROM TabelaY WHERE ID = $ID";
 $result = mysqli_query($conn, $sql);

 if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
    $row1 = $row["ID"];
    $row2 = $row["Nome"];
    $row3 = $row["Dados"];

        mysqli_select_db($conn,"Servidor1");

        $sql = "INSERT INTO TabelaX (ID, Nome, Dados)
        VALUES ($row1, $row2, $row3);";
        if (mysqli_multi_query($conn, $sql)) {
            echo "New records created successfully";
        } else {
            echo "Error: " . $sql . "<br>" . mysqli_error($conn);
        }

    }
} else {
   echo "0 results";
 }

mysqli_close($conn);
?>

Answer 1

From what I have read, your current approach might be the only way to do this using regular queries with MySQL. But, if you were doing this directly on MySQL, you could just use a simple INSERT INTO ... SELECT:

INSERT INTO db1.TabelaX (ID, Nome, Dados)
SELECT ID, Nome, Dados
FROM db2.TabelaY;

One possibility would be to create a stored procedure on MySQL which does the above insert, and then call it from your PHP code:

$result = mysqli_query($conn, 
    "CALL YourProcName") or die("Query fail: " . mysqli_error());