The SQL SELECT INTO Statement
Using the SQL SELECT INTO statement to transfer data from one table into a newly created table in sql, It means that this operation is used to create a new table that contains specific data copied from existing tables.
Here, we will learn using two common SQL database systems: MySQL and SQL Server.
Examples of SQL SELECT INTO Statement
Syntax for Oracle:
SELECT INTO statement syntax:
Copy all columns from oldtable into a new table.
SELECT column1, column2, column3,…
INTO newtable
FROM oldtable
WHERE condition;
NOTE: Newtable and oldtable are just table names.
The following sql select INTO statement copied only a few columns into a new table:
SELECT StudentName, StudentClass, StudentAddress INTO Students2023
FROM Students;
The following sql select INTO statement copies only the Student Failed into a new table:
SELECT * INTO StudentFailed
FROM Students
WHERE Student = ‘Failed’;
MySQL Example:
Let’s say you have a table called “Employees” and you want to create a new table called “HighSalaryEmp” to store data about employees with a salary greater than $100,000.
—-Create a new table called HighSalaryEmp
CREATE TABLE HighSalaryEmp(
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL (10, 2)
);
—Fetch the new table with data from the Employees table
INSERT INTO HighSalaryEmp (FirstName, LastName, Salary)
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 100,000;
In SQL Server, the SELECT INTO statement simplifies the query by creating a new table with data in a single statement. Using the above same scenario.
—-Create a new table called HighSalaryEmp with data
SELECT FirstName, LastName, Salary
INTO HighSalaryEmp
FROM Employees
WHERE Salary > 100,000;
We directly use the SELECT INTO statement to create the “HighSalaryEmp” table and populate it with data from the “Employees” table based on the specified condition.