Learn Basics and Use of SQL – Into Statement

In this tutorial we are going to learn about SQL – Into Statement.

Usages :

• It is used to Copy Table Data.
• It is used to insert bulk record at once from existing table.
• It inserts entire result set into a table at once.

Operation on INTO

• Select Into.
• Insert Into Select.

1) Select Into

• It creates a new table and inserts all rows from the select statement.
• It can also be used to copy particular data from existing table with combination of select statement into new table.
• It can also be used to copy specific column from select statement into new table.
• No need to create a new table to insert the data from old table

Syntax of Select Into :
Select Into statement have following syntax:

  select <column_list> into <newtablename>
  from <old_table>
  [where...]

Example: In following example I am going to copy the tblsalary data into new table called tblsalary_test by using select into statement

Let’s see the data in tblsalary table

Now to copy the tblsalary data into new table we just need to add INTO statement. You don’t need to create a new table to insert the data from existing table.

You can see that Into statement has inserted 5 row to new table tblsalary_test from old table. It has quickly inserted entire result from single statement at once.
The best use of into statement is that I can use for bulk insert.

2) Insert Into Select :

• It inserts the data into existing table.
• It is used to copy the data (Row/column) from table into existing table.
• It is use for bulk insert to existing table
• It is used to insert particular row or copy particular column from table into existing table.

Syntax of Insert Into Select :

insert into <existing_table>
select <column_list> 
from <old_table>
[where...]

Example: In following we have two table tblsalary and tbldeptsalary.

The goal is that we have to copy the entire row from tbldeptsalary into our existing table tblsalary.

To achieve this goal let’s create our insert into query.

insert into tblsalary
select empname,pay from tbldeptsalary

By executing above query, you can see following that insert into select statement has copied the entire result set to into our existing table.

Thus we have learnt the basic use of Sql Into statement.