SQL Insert Into Select Statement

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

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

Syntax

The syntax of the SQL Server INSERT INTO SELECT Statement is

INSERT INTO [DestinationTable] ([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.
  • DestinationTable: Please provide the fully qualified name along with the column names.
  • Source: Use or Select one or more tables present in the Database. Use JOINS to join multiple tables.

Here, we have SelectInto and Employee tables, and they are empty. Our task is to get the Data present in the Employee and Department tables in the test database and put it into Tutorials Database using SQL INSERT INTO SELECT Statement.

Two Different Databases and List of Tables in them 1

and the Data inside the Employee is

Employee Table 0

The Data inside the Department is

Department Table Rows 98

SQL Insert Into Select Statement for All Columns

We will choose all the Columns present in the Employee table and insert them into the [Employee] in the Database.

INSERT INTO [dbo].[Employee]([FirstName],[LastName],[DepartID])
SELECT [FirstName],[LastName],[DepartID]
FROM [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. It means the above INSERT INTO Select can also be written as:

INSERT INTO [dbo].[Employee]
SELECT [FirstName],[LastName],[DepartID]
FROM [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 and load them into [Employee] in the Database.

(15 rows(s) affected)

Let us see whether it loaded the selected data into the destination or not.

SQL INSERT INTO SELECT Statement 3

Few Columns

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

INSERT INTO [dbo].[Employee]([FirstName],[LastName])
SELECT [FirstName],[LastName]
FROM [Employee]

From the below 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 4

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

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

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

(3 rows(s) affected)

Let us see whether the above query loaded the chosen data into the destination or not.

View Records 6

Insert Into Select from Multiple Tables

In this Insert Into Select Statement example, we pick columns present in both the Employee and Department, then load them into a table. Here we are using the INNER JOIN for joining the two tables using the id column.

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

Let us see whether this Statement loaded the specified data into the destination or not.

SQL INSERT INTO SELECT from Muliple Tables 8

Comments are closed.