SELECT INTO Statement in SQL

SELECT INTO statement in SQL is generally used for bulk copy purposes. We could copy the whole data from one table into another table using a single command. In very simple terminology we can say that select into statement simply copies data from one tablet to another table in a very simple way.

Note: The queries are executed in SQL SERVER and they may not work in many online SQL editors so better use an offline editor.

Syntax:

SELECT column1, column2… 

INTO NEW_TABLE from SOURCE_TABLE

WHERE Condition;

Parameter Explanation

• NEW_TABLE should have the same schema and data types as that SOURCE_TABLE. 
• INTO means in which table we need to copy.
• Condition: A condition is an optional statement that tells you which rows to pick. If you don’t specify a condition, you’ll just pick all the rows from the original table.

Select INTO Examples

Let’s first create a table Customer and insert some random data:

Query

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output

Customer Table

Query

Now,for inserting a data first we need to create a backupCustomer Table and then we will insert data in that table.

 To copy all the data from Customer into the backUpCustomer table.

CREATE TABLE backUpCustomer (
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age INT,
    Phone VARCHAR(10)
);

INSERT INTO backUpCustomer (CustomerName, LastName, Country, Age, Phone)
SELECT CustomerName, LastName, Country, Age, Phone
FROM Customer;
Select * FROM backUpCustomer ;

Output

backUp Customer Table

Query

Use the ‘where’ clause to copy only some rows from Customer into the backUpCustomer table.

CREATE TABLE OtherTable (
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age INT,
    Phone VARCHAR(10)
);

INSERT INTO OtherTable (CustomerName, LastName, Country, Age, Phone)
SELECT CustomerName, LastName, Country, Age, Phone
FROM Customer
WHERE Country = 'India';

Output

output

Query

 To copy only some columns from Customer into the backUpCustomer table specify them in the query.

CREATE TABLE backUpCustomers (
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

INSERT INTO backUpCustomers (CustomerName, LastName, Age)
SELECT CustomerName, LastName, Age
FROM Customer;


SELECT * FROM backUpCustomers;

Output

Output

Insert INTO SELECT vs SELECT INTO

Both statements could be used to copy data from one table to another. But INSERT INTO SELECT could be used only if the target table exists whereas SELECT INTO statement could be used even if the target table doesn’t exist as it creates the target table if it doesn’t exist.

INSERT INTO backUpCustomer select * from Customer;

HERE table tempTable should be present or created beforehand else throw an error.

SELECT * INTO backUpCustomer from Customer;

Here it’s not necessary to exist before as SELECT INTO creates a table if the table doesn’t exist and then copies the data.