SQL - Select Into Statement

---

---

The SQL Select Into Statement

The SQL SELECT INTO Statement creates a new table and inserts data from an existing table into the newly created table. The new table is automatically created based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database.

However, it's important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary.

MySQL doesn't support the SELECT ... INTO TABLE Sybase SQL extension i.e. in MySQL you cannot use the SELECT ... INTO statement to insert data from one table to another. Instead of this, we can use INSERT INTO ... SELECT statement or, CREATE TABLE ... SELECT.

Syntax

Following is the basic syntax of the SQL SELECT INTO statement in SQL Server −

SELECT * INTO new_table_name FROM existing_table_name

Example

Let us create the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The CUSTOMERS table will be creates as follows −

The following SELECT INTO statement creates a new table called CUSTOMER_BACKUP and copies the data from the CUSTOMERS table into it −

SELECT * INTO CUSTOMER_BACKUP FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table −

SELECT * from CUSTOMER_BACKUP;

The table displayed is as follows −

Copying Data From Specific Columns

We can also copy data from specific columns from an existing table into the new table using the SQL SELECT INTO statement. To do so, we just need to include the required column names after the select keyword.

Syntax

Following is the syntax −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM existing_table_name;

Example

In the following query, we are creating a new table called CUSTOMER_DETAILS with only the NAME, AGE, and ADDRESS columns from the CUSTOMERS table, and populate it with the corresponding data.

SELECT name, age, address 
INTO CUSTOMER_DETAILS 
FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table −

SELECT * from CUSTOMER_DETAILS;

The table displayed is as follows −

Note: The new table will not include any other columns from the original table. Also the original table remains unchanged.

Copying Data From Multiple Tables

Using the SQL SELECT INTO statement we can also copy data from multiple tables to a new table. This is accomplished using the JOIN clause which combines the data from multiple tables (based on a common column).

Syntax

Following is the syntax to copy data from multiple tables using the SELECT INTO statement −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM table1
JOIN table2 ON table1.column = table2.column

Example

First of all, let us create another table named ORDERS −

CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2));

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is created as −

Now, we are creating a new table called CUSTOMER_ORDERS that includes the customer name from the CUSTOMERS table and the customer id from the ORDERS table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table −

SELECT CUSTOMERS.Name, ORDERS.customer_id
INTO CUSTOMER_ORDERS
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id;

Output

We get the following result. We can observe that 8 rows have been modified.

(8 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_ORDERS table −

SELECT * FROM CUSTOMER_ORDERS;

The table displayed is as follows −

Copying Specific Records

We can also use the SQL SELECT INTO statement with a WHERE clause to create a new table and copy specific rows from an existing table into it.

Syntax

Following is the syntax for using SELECT INTO statement with a WHERE clause −

SELECT *
INTO new_table_name
FROM existing_table_name
WHERE condition;

Example

Using the following query we are creating a new table called NameStartsWith_K that includes all columns from the CUSTOMERS table, but it only stores the records of the customers whose name starts with "k".

SELECT *
INTO NameStartsWith_K
FROM CUSTOMERS
WHERE NAME LIKE 'k%';

Output

We get the following result. We can observe that 3 rows have been modified.

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

SELECT * from NameStartsWith_K;

The table displayed is as follows −