Syntax SQL INSERT INTO SELECT Statement:
to copy all data from one table to another table use this below syntax
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
to copy only few column data from one table to another table use this below syntax
INSERT INTO table2 (column name list)
SELECT column name list
FROM table1
WHERE condition;
Lets Learn.
1.Create table in database as college_student table and copy all data from student table to college_student table
CREATE TABLE `college_student` (
`StudentID` int(11) NOT NULL,
`StudentName` varchar(100) NOT NULL,
`ParentsContact` varchar(100) NOT NULL,
`Address` varchar(100) NOT NULL,
`Country` varchar(100) NOT NULL,
`Section` varchar(100) NOT NULL,
`Branch` varchar(50) NOT NULL,
`Percentage` int(11) NOT NULL,
`Fee` int(11) NOT NULL,
`CourseID` varchar(220) NOT NULL
)
Student table.
StudentID | StudentName | Address | Branch | Percentage | Fee | CourseID |
---|---|---|---|---|---|---|
1 | Liam | 23 Andrew road | ISE | 66 | 66000 | 5 |
2 | Andrew | 2415 Anthony market str | ISE | 80 | 65000 | 6 |
4 | Ryan | 18 Christopher sq. | ME | 67 | 70000 | 1 |
5 | Charles | 878 Eli Berguvsvägen 8 | ECE | 52 | 75000 | 8 |
8 | Anikth | 23 Andrew road | Civil | 66 | 66000 | 3 |
9 | Andrew | 2415 Anthony market str | Civil | 80 | 65000 | 3 |
10 | Bhavya | 18 Christopher sq. | Civil | 67 | 7000 | 3 |
Example(1)
INSERT INTO college_student SELECT * FROM student;
using above query copies all columns from student table to college_student table
INSERT INTO college_student (StudentName, Address, Country)
SELECT StudentName,Address,Country FROM student
Example(3)
INSERT INTO college_student (StudentName, Address, Country)
SELECT StudentName, Address, Country FROM student WHERE Country='Germany'
using above query copies only StudentName ,Address ,Country columns from student table to college_student table if country is equal to Germany.
2.Complete code of SQL INSERT INTO SELECT Statement in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL INSERT INTO SELECT Statement</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<div class="text-center">
<h1>SQL INSERT INTO SELECT Statement</h1>
</div>
<br>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if($connect === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql_1 = "INSERT INTO college_student SELECT * FROM student";
//$sql_2 = "INSERT INTO college_student (StudentName, Address, Country) SELECT StudentName,Address,Country FROM student";
// $sql_3 = "INSERT INTO college_student (StudentName, Address, Country) SELECT StudentName, Address, Country FROM student WHERE
Country='Germany'";
$sql_4 = "Select * from college_student;";
if(mysqli_query($connect, $sql_1)){
echo "<br><h1 class='text-success'>Data copied successfully.</h1>";
} else{
echo "ERROR: Could not able to execute $sql_1. " . mysqli_error($connect);
}
if($result_1 = mysqli_query($connect, $sql_4)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>Branch</th>
<th>Percentage</th>
<th>Fee</th>
<th>CourseID</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>".$row['StudentID'] /*student table*/ ."</td>
<td>".$row['StudentName']."</td>
<td>".$row['Address']."</td>
<td>".$row['Branch']."</td>
<td>".$row['Percentage']."</td>
<td>".$row['Fee']."</td>
<td>".$row['CourseID']."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
mysqli_close($connect);
?>
</div>
<br>
</div>
</body>
</html>