INSERT INTO SELECT Statement In SQL Server

INSERT INTO SELECT statement in SQL Server is used to copy the data from the source table and insert it into the target table. But, before copying the data, the target table must exist in the database.

Key Points

  • The INSERT INTO SELECT statement requires the target table to exist in the database before data can be transferred from the source table to the target table.
  • The data types in the source and target tables must match for the INSERT INTO SELECT statement to work.

Syntax

INSERT [TOP (expression [PERCENT])]
INTO <target_table> (<column_name>)
SELECT * FROM <source_table>
[WHERE condition]

Note:

The TOP clause allows you to specify the number of rows returned by the query to insert into the target table. If you use the PERCENT option, the statement will insert a percentage of the rows instead.



SELECT INTO Statement In SQL Server

The SELECT INTO statement in SQL Server is used to copy data from one table to a new table.

Key Points

  • The new table does not need to exist in the database to transfer data from the source table.
  • A new table with the structure is automatically created while executing this command.
  • The new table will be created with the column names and types defined in the source table.
  • Users can also create new column names using the "AS" clause.
  • SELECT INTO statement cannot be used to insert data into an existing table.
  • SELECT INTO statement does not copy constraints such as primary key and indexes from the source_table to the new_table.

Syntax

SELECT <select_list>
INTO <new_table>
FROM <source_table>
[WHERE condition]


The SELECT INTO statement in SQL Server, selects data from one table and inserts it into a new table.

SELECT INTO statement in SQL Server can do the following
1. Copy all rows and columns from an existing table into a new table. This is extremely useful when you want to make a backup copy of the existing table.

SELECT * INTO EmployeesBackup FROM Employees


2. Copy all rows and columns from an existing table into a new table in an external database.

SELECT * INTO HRDB.dbo.EmployeesBackup FROM Employees


3. Copy only selected columns into a new table

SELECT Id, Name, Gender INTO EmployeesBackup FROM Employees


4. Copy only selected rows into a new table

SELECT * INTO EmployeesBackup FROM Employees WHERE DeptId = 1


5. Copy columns from 2 or more table into a new table

SELECT * INTO EmployeesBackup
FROM Employees
INNER JOIN Departments
ON Employees.DeptId = Departments.DepartmentId


6. Create a new table whose columns and datatypes match with an existing table. 

SELECT * INTO EmployeesBackup FROM Employees WHERE 1 <> 1


7. Copy all rows and columns from an existing table into a new table on a different SQL Server instance. For this, create a linked server and use the 4 part naming convention

SELECT * INTO TargetTable
FROM [SourceServer].[SourceDB].[dbo].[SourceTable]


Please note : You cannot use SELECT INTO statement to select data into an existing table. For this you will have to use INSERT INTO statement.

INSERT INTO ExistingTable (ColumnList)
SELECT ColumnList FROM SourceTable