How can INSERT INTO ... SELECT ... be used to insert data into multiple tables in MySQL?

When using INSERT INTO ... SELECT ..., you can insert data into multiple tables in MySQL by selecting the desired columns from the source table and specifying the corresponding columns in the destination tables within the SELECT statement. This allows you to populate multiple tables with data from a single source table in a single query. 

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Insert data into multiple tables
$sql = "INSERT INTO table1 (col1, col2)
        SELECT col1, col2 FROM source_table;
        
        INSERT INTO table2 (col3, col4)
        SELECT col3, col4 FROM source_table;";
        
if ($conn->multi_query($sql) === TRUE) {
  echo "Data inserted into multiple tables successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Keywords

PHP MySQL INSERT INTO SELECT multiple tables

Related Questions