SELECT INTO Statement

The SELECT INTO statement is used to create a new table from the existing table. The new table will be created with the same column structure (table structure) as the existing table along with its data. The new table copies the table structure as well as the data of the existing table.

The new table must have a different table name with respect to the existing table name when both tables belong to the same database.
If We want to create a new table in a different database other than the existing database, Then both databases must be connected with the linked service. If we don’t have a linked service between them then it is not possible to create a new table into a different database.

The new table generated using the SELECT INTO statement does not have any constraints, indexes, or other properties of the original table. And the new table doesn’t have a primary key and foreign key by default. We have to create manually as per the requirements.

SELECT INTO statement

 

Use Cases for SELECT INTO

  • Data Backup and Archiving: The SELECT INTO statement is ideal for creating backups or archiving specific data subsets from a larger table.
  • Data Transformation: You can use SELECT INTO to transform and format data while transferring it to a new table.
  • Temporary Data Storage: SELECT INTO is useful for storing temporary data results for further analysis or processing.
  • Aggregating Data: Create summary tables by aggregating data using functions like COUNT, SUM, AVG, etc., with the SELECT INTO statement.

Best Practices for Using SELECT INTO

  • Explicit Column Names: Specify the column names explicitly in the SELECT clause for better maintainability and to avoid unexpected issues.
  • Data Validation: Ensure that the data types of the columns in the SELECT clause match the data types in the new_table.
  • Temporary Table Naming: Since SELECT INTO creates a new table, it’s a good practice to use meaningful names to avoid confusion.
  • Indexes and Constraints: To maintain data integrity, consider adding indexes and constraints to the new_table as required.

Optimizing SELECT INTO Performance

  • Minimize Data Transfer: Select only the necessary columns to minimize data transfer and enhance performance.
  • Limit Rows: Use WHERE conditions to limit the number of rows transferred if the source table is large.
  • Temporary Tables: If possible, use temporary tables for intermediate storage to reduce the overhead of creating permanent tables

SELECT INTO Statement – Copy Data From All Columns

Select into statement is used to copy data and columns from the existing table to the new table. Below is the syntax to copy data into a new table from the existing table.

Syntax

SELECT *
INTO newtable_name
FROM existingtable_name

Example

To understand let us create the employeedits table which contains the personal details of employees including their name, age, salary, etc. as below −

CREATE TABLE dbo.employeedits (
   NAME VARCHAR (50) NOT NULL,
   AGE INT NOT NULL,
   SALARY DECIMAL (18, 2),       
);

Now we have to insert values into employeedits table using the INSERT statement as follows −

INSERT INTO dbo.employeedits  (NAME,AGE,SALARY)
VALUES ( 'Mark', 35, 20000.00 );

INSERT INTO dbo.employeedits  (NAME,AGE,SALARY)
VALUES ( 'Reva', 30, 10000.00 );

INSERT INTO dbo.employeedits  (NAME,AGE,SALARY)
VALUES ( 'Daren', 28, 5000.00 );

INSERT INTO  dbo.employeedits  (NAME,AGE,SALARY)
VALUES ( 'Dune', 26, 4000.00 );

INSERT INTO  dbo.employeedits  (NAME,AGE,SALARY)
VALUES ( 'Rohit', 38, 25000.00 );

You can retrieve the data of employeedits table you will get the below output as a result

Query:-
select * from dbo.employeedits

Queryoutput:-

NAME                                               AGE         SALARY
-------------------------------------------------- --------------------------
Mark                                               35          20000.00
Reva                                               30          10000.00
Daren                                              28          5000.00
Dune                                               26          4000.00
Rohit                                              38          25000.00

Now we will create a new table that is employeedits_bkp which will act as a backup for the employeedits table. We will create this table using the select into statement.

select *
 into  dbo.employeedits_bkp
 from dbo.employeedits

We can see that the table employeedits_bkp is created along with the data with the help of employeedits table.

Query:-

 select * from dbo.employeedits_bkp;

Queryoutput:-

NAME                                               AGE         SALARY
-------------------------------------------------- ------------------------
Mark                                                35          20000.00
Reva                                                30          10000.00
Daren                                               28          5000.00
Dune                                                26          4000.00
Rohit                                               38          25000.00
Raman                                               40          29000.00
raj                                                 44          30000.00
mohit                                               50          40000.00

 

SELECT INTO  Statement- Copy Data From Specific Columns

We can create a table using specific columns depending upon the requirements of an existing table using the select into a statement.

Syntax
Below is the basic syntax to copy specific columns into a new table using the SELECT INTO Statement −

SELECT column1, column2, ..., columnN
INTO newtable_name
FROM existingtable_name;

Example

In the below query, we create a new table called “employeeditsDetails” with only the “name”, and “age” columns from the “employeedits” table, and fetch it with the corresponding data.

select name, age
 into dbo.employeeditsDetails
 from dbo.employeedits

Below is the query and the output for the table employeeditsDetails.

Query:-

 select * from dbo.employeeditsDetails

Queryoutput:-

name                                      age
-------------------------------------------------- 
Mark                                      35
Reva                                      30
Daren                                     28
Dune                                      26
Rohit                                     38

The new table doesn’t include any other column from the existing table. And there is no impact on the original table.

 

SELECT INTO Statement – Copy Data From Multiple Tables

We can create a new table from multiple original tables using select into statements. Here, the columns are fetched from multiple tables using join operations.

syntax
Below is the syntax to copy data from multiple tables.

SELECT column1, column2, ..., columnN
INTO newtable_name
FROM table1 t1
inner JOIN 
table2 t2
 ON  t1.column = t2.column

example

In order to achieve data insertion from multiple tables into a new table we will create another table that is “Detpdts” which will store details about employee department details.

 CREATE TABLE dbo.Detpdts (
  empname varchar(50),
  deptname varchar(50)
)

Using select statement insert data into Detpdts table and insert query as below

insert into dbo.Detpdts  (empname,deptname) values('Mark','sales')
insert into dbo.Detpdts  (empname,deptname) values('Reva','sales')
insert into dbo.Detpdts  (empname,deptname) values('Daren','sales')
insert into dbo.Detpdts  (empname,deptname) values('Dune','store' )
insert into dbo.Detpdts  (empname,deptname) values('Rohit','account')

insert query output will be as below.

Query:-
 select * from dbo.Detpdts

Queryoutput:-

empname                                            deptname
--------------------------------------------------------------- 
Mark                                               sales
Reva                                               sales
Daren                                              sales
Dune                                               store
Rohit                                              account

 

Now we will create “employeeinfo” table using Detpdts and employeedits table. The new table will contain columns such as employee name, age, and department name.The employee name, and age will be fetched from employeedits table and the department name will be fetched from Detpdts table.

select e.NAME,e.age,d.empname
into dbo.employeeinfo
from dbo.employeedits  e
inner join dbo.Detpdts d
on d.empname=e.NAME

In the above query e and d are used as an alias to the table for better understanding and reduce code complexity. the join condition is on the employee name column because it’s a key column between both tables.

Query:-

select * from  dbo.employeeinfo

Queryoutput:-

NAME                                               age         empname
---------------------------------------------------------------------------
Mark                                               35           Mark
Reva                                               30           Reva
Daren                                              28           Daren
Dune                                               26           Dune
Rohit                                              38           Rohit

 

SELECT INTO statement – Copy data using WHERE clause

We can use a where clause along with the select into statement. It is used to fetch only required data from the original table to the new table. Only those records or rows will we insert which satisfy the given where condition.

syntax

following is the syntax to Copy data using the WHERE clause.

SELECT *
INTO  newtable_name
FROM existingtable_name
WHERE condition;

Now we will create a table “deptsales” which will copy sales data from Detpdts table.

select *
into dbo.deptsales
from dbo.Detpdts
where deptname='sales'

Below is the query output for the deptsales table.

Query:-
select * from  dbo.deptsales

Queryoutput:-

empname                                         deptname
------------------------------------------------------------
Mark                                               sales
Reva                                               sales
Daren                                              sales
Similar Reads

What is SELECT Statement?

What is join?

What are commands in SQL?

What is index?

What is subquery?