SQL Insert Into Select Statement

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

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

SQL Insert Into Statement 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 qualifies name along with the column names.
  • Source: 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 Select 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 Table is

Employee Table 0

The Data inside the Department is

Department Table Rows 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 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 SQL 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 insert into [Employee] in the Database.

(15 rows(s) affected)

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

SQL INSERT INTO SELECT Statement 3

Insert Few Columns

Here, we select a few Columns present in the Employee table 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

SQL INSERT INTO SELECT Statement 4

This SQL Insert Into Select Statement example select a few Rows present in the Employee and put them into a destination table. 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 transact sql query loaded the chosen 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.

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 selected data into the destination or not.

SQL INSERT INTO SELECT from Muliple Tables 8

Comments are closed.