SQL Select Into

Filed Under: SQL

SQL Select Into

SQL SELECT INTO operator is a very useful operator when it comes to data migration. SELECT INTO is used for the following two purposes.

  1. SELECT INTO copies data from all the columns of one table to new table.
  2. SELECT INTO copies data only from selected columns of one table to a new table.

We will try to understand both the usage in detail in below-mentioned sections.

All Columns in New Table

We will try to understand the syntax for using SQL SELECT INTO for copying all the columns from one table to a new table.
Select Into Syntax

SELECT * INTO new_table FROM old_table_name WHERE condition;

In the syntax above all the columns data is retrieved based on the WHERE clause and a new table is created with the name provided.

Let’s consider the following Customer Table to understand SELECT INTO command for copying all the columns data based on a condition.

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
3 Annie 35 F
4 Tom 38 M

Scenario: Create a new table from the Customer table where customer gender is male.

SELECT * INTO MaleCustomer FROM Customer WHERE CustomerGender = 'M';

Output:

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
4 Tom 38 M

We can also copy data of all the columns in a new table in a different database using IN clause from an old table.

SQL Select Into For All Columns

SQL Select Into For All Columns

 

After SQL Select Into For All Columns

After SQL Select Into For All Columns

Syntax:

SELECT * INTO new_table IN external_db FROM old_table_name WHERE condition

Let’s try to understand the above-mentioned syntax using below mentioned example.

Scenario: Create a table in the backup database from customer table where customer gender is male.

SELECT * INTO MaleCustomer IN 'backup.mdb' FROM Customer WHERE CustomerGender = 'M';

Output: 3 rows affected

The newly generated table will look as shown below and will be generated in backup.mdb schema.

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
4 Tom 38 M

Selective Columns in New Table

We will try to understand the syntax for using SQL SELECT INTO for copying selective columns from one table to a new table.

Syntax:

SELECT column_name(s) INTO new_table FROM old_table_name WHERE condition;

In the syntax above selective columns data is retrieved based on the WHERE clause and a new table is created with the name provided.

Scenario: Create a new table from Customer table with only customer name and age where customer gender is male.

SELECT CustomerName, CustomerAge INTO TestCustomer FROM Customer WHERE CustomerGender = 'M';

Output: 3 rows affected

CustomerName CustomerAge
John 31
Amit 25
Tom 38
SQL Select Into For Selected Columns

SQL Select Into For Selected Columns

 

After SQL Select Into For Selected Columns

After SQL Select Into For Selected Columns

We can also create an empty table which will have the same schema of the old table by providing a where condition which does not return any value.

SELECT * INTO NewCustomer FROM Customer WHERE 1=0;

The above query will result in a table with the name as NewCustomer which will have the same schema as Customer table.

MySQL Select Into

MySQL doesn’t support Select Into clause. So we can use below query to create a table by selecting data from another table.

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

NOTE: In the above query, AS is optional.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages