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.
- SELECT INTO copies data from all the columns of one table to new table.
- 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
|
1 2 |
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.
|
1 2 |
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
After SQL Select Into For All Columns
Syntax:
|
1 2 |
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.
|
1 2 |
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:
|
1 2 |
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.
|
1 2 |
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
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.
|
1 2 |
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.
|
1 2 |
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; |
NOTE: In the above query, AS is optional.