Syntax for SELECT INTO
SELECT * used to copy all columns in old table.
SELECT *
INTO newtable
FROM oldtable
WHERE condition;
SELECT column_name list used to select only few columns from old table.
SELECT column_name list
INTO newtable
FROM oldtable
WHERE condition;
Lets Learn.
1.Create table in database as backUpStudents table and copy all data from student table to backUpStudents table
CREATE TABLE `backupstudents` (
`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,
`DOB` date 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)
SELECT * INTO backUpStudents from student;
SELECT StudentID,StudentName, INTO backUpStudents
FROM student;
Select * from backUpStudents;//to check copied data
MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension.Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing. See Section INSERT , “INSERT ... SELECT Syntax”.
2.Complete code of SQL SELECT INTO in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title> SQL SELECT INTO 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 SELECT INTO 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 = "SELECT * INTO backUpStudents from student";
$sql_2 = "Select * from backUpStudents;";
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_2)){
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>