SQL Insert Into Select Statement

The SQL Insert Into Select Statement can be used to insert the data into SQL Server tables. This Sql Server Insert into Select Statement will insert the records selected by the SELECT Statement into the existing table.

If you have the existing tables in the destination table, then SQL Insert into Select Statement can be very useful to insert the selected data into a destination table. If the table is not created then use the SELECT INTO Statement

SQL Insert Into Statement Syntax

The syntax of the INSERT INTO SELECT Statement in SQL Server is

-- SQL Server INSERT INTO SELECT Statement
INSERT INTO [Destination Table] ([Column Names])
SELECT [Column Names]
FROM Source
WHERE Condition --This is optional
  • Columns: It allows us to choose the number of columns from the SQL Server tables. It may be One or more.
  • Destination Table: Please provide the fully qualifies Table name along with the column names.
  • Source: One or more tables present in the Database. JOINS are used to join multiple tables.

Here, we have [Select Into] and Employee tables in the [SQL Server Tutorials], and they are empty. Our task is to Select the Data present in the Employee and Department tables in the [SQLTEST] database and insert into [SQL Server Tutorials] Database using SQL INSERT INTO SELECT Statement.

SQL INSERT INTO SELECT Statement 1

and the Data inside the [SQLTEST] Employee Table is

SQL INSERT INTO SELECT Statement 0

The Data inside the Department Table is

SQL INSERT INTO SELECT Statement 98

SQL Insert Into Select Statement to Insert All Columns

We will select all the Columns present in the Employee table and insert them into [Employee] Table in the [SQL Server Tutorials] Database.

-- SQL Server INSERT INTO SELECT Statement
USE [SQL Server Tutorials]
GO
INSERT INTO [dbo].[Employee]([FirstName],[LastName],[DepartID])
SELECT [FirstName],[LastName],[DepartID]
FROM [SQLTEST].[dbo].[Employee]

If we know that the number of columns in the source table is exactly matching with the columns in the SELECT Statement, then we can ignore the column names of the destination table. It means the above SQL INSERT INTO Select can also be written as:

-- SQL Server INSERT INTO SELECT Statement
INSERT INTO [dbo].[Employee]
SELECT [FirstName],[LastName],[DepartID]
FROM [SQLTEST].[dbo].[Employee]

TIP: It is not good practice to ignore the column names so, always provide the column names.

The above query will Select the [FirstName], [LastName], and [DepartID] columns from the source table and insert into [Employee] table in the [SQL Server Tutorials] Database.

SQL message

(15 rows(s) affected)

Let us see whether the INSERT INTO Statement inserted the selected data into the destination table or not

SQL INSERT INTO SELECT Statement 3

SQL Insert Into Select Statement to Insert Few Columns

Here, we select a few Columns present in the Employee table and insert them into a destination table. Although we have three columns in the destination table, let us insert only [FirstName] and [LastName].

-- SQL Server INSERT INTO SELECT Statement
USE [SQL Server Tutorials]
GO

INSERT INTO [dbo].[Employee]([FirstName],[LastName])
SELECT [FirstName],[LastName]
FROM [SQLTEST].[dbo].[Employee]
SQL INSERT INTO SELECT Statement 4

From the above screenshot, you can see that the Data inserted only for [FirstName] and [LastName] columns and NULL values for the [DepartID] columns

Insert Into Select Statement to Insert Few Rows

This SQL Insert Into Select Statement example select a few Rows present in the Employee table and insert them into a destination table. Here we are going to restrict the rows using the WHERE Clause.

-- SQL Server INSERT INTO SELECT Statement
USE [SQL Server Tutorials]
GO

INSERT INTO [dbo].[Employee]([FirstName],[LastName],[DepartID])
SELECT [FirstName],[LastName],[DepartID]
FROM [SQLTEST].[dbo].[Employee]
WHERE [DepartID] = 2

Above T-SQL query will insert [FirstName], [LastName] and [DepartID] columns from the source data where [DepartID] value is equal to 2 into the [Employee] table.

SQL Message

(3 rows(s) affected)

Let us see whether the INSERT INTO Statement inserted the selected data into the destination table or not

SQL INSERT INTO SELECT Statement 6

SQL Insert Into Select from Multiple Tables

In this SQL Server Insert Into Select Statement example, we select Columns present in both the Employee table and Department table, then insert them into [Select Into] table. Here we are using the INNER JOIN for joining the two tables using id column.

-- SQL Server INSERT INTO SELECT Statement
USE [SQL Server Tutorials]
GO

INSERT INTO [dbo].[Select Into]([FirstName],[LastName],[DepartmentName])
SELECT EMP.[FirstName]
      ,EMP.[LastName]
      ,DEPT.[DepartmentName]
FROM [SQLTEST].[dbo].[Employee] EMP
INNER JOIN
[SQLTEST].[dbo].[Department] DEPT ON
EMP.id = DEPT.i

SQL Query Execution Message

(8 rows(s) affected)

Let us see whether the Insert Into Select Statement inserted the selected data into the destination table or not.

SQL INSERT INTO SELECT Statement 8

Comments are closed.