SQL SELECT INTO Statement 6

SQL SELECT INTO Statement

by

The SQL SELECT INTO statement can be used to insert the data into tables. The select into in SQL first creates a new table. Then this SQL SELECT INTO inserts the selected rows by the Select Statement into that new table.

If we haven’t created the destination table, then the SQL Server Select Into statement can be handy to insert the data into the destination table. If you have an existing table, then use Insert Into Select Statement.

SQL SELECT INTO Syntax

The SELECT INTO in SQL Server can be written as:

-- SQL Server SELECT INTO Statement
SELECT [Column Names]
INTO [New Table]
FROM Source
WHERE Condition --This is optional
  • Columns: It allows us to choose the number of columns from the tables. It may be one or more.
  • New Table: Please specify the fully qualifies Unique Table name for SQL Server.
  • Source: One or more tables present in the Database. Use JOINS to join multiple tables.

From the below screenshot, you can see that We don’t have any Department or Employee table in the [SQL Server Tutorials]. Our task is to Select the Data present in that table from the [SQLTEST] Database and insert it into [SQL Server Tutorials] Database using SQL SELECT INTO Statement.

SQL SELECT INTO Statement 1

Data inside the Employee Table is

SQL SELECT INTO Statement 2

Data inside the Department Table is

SQL SELECT INTO Statement 3

SQL SELECT INTO Insert All Columns

Here, we select all the Columns present in the Department table and insert them into [SQL Server Tutorials] Database.

-- SQL Server SELECT INTO Statement
SELECT [id]
      ,[DepartmentName]
INTO [SQL Server Tutorials].[dbo].[Department]
FROM [SQLTEST].[dbo].[Department]

OR We can use the asterisk (*) as well. But it is not good practice to use an asterisk, so always provide the column names.

-- SQL Server SELECT INTO Statement
SELECT *
INTO [SQL Server Tutorials].[dbo].[Department]
FROM [SQLTEST].[dbo].[Department]

The above query creates a new [Department] table and inserts all the records from the source data. The SQL Execution Message:

Messages
-------
(8 rows(s) affected)

Let us see whether the table created with data in the destination database or not

SQL SELECT INTO Statement 5

SELECT INTO Insert Few Columns

In this Sql Server Select Into example, We select a few Columns present in the Employee table and insert them into [SQL Server Tutorials] Database. Here, we restrict the rows as well using the WHERE Clause.

-- SQL Server SELECT INTO Statement
SELECT [FirstName]
      ,[LastName]
      ,[DepartID]
INTO [SQL Server Tutorials].[dbo].[Employee]
FROM [SQLTEST].[dbo].[Employee]
WHERE [DepartID] = 1

Above query creates a new table called [Employee] and insert [FirstName], [LastName] and [DepartID] columns from the source data, where [DepartID] value is equal to 1. SQL Message

Messages
-------
(4 rows(s) affected)

Let’s see whether the table created with data in the target database or not

SQL SELECT INTO Statement 7

SQL SELECT INTO Insert Data from Multiple Tables

This Select Into example select Columns present in both the Employee table and Department table. Next, insert them to [SQL Server Tutorials] Database. Here, we are using the INNER JOIN for joining the two tables using id column.

-- SQL Server SELECT INTO Statement
SELECT EMP.[FirstName]
      ,EMP.[LastName]
      ,DEPT.[DepartmentName]
INTO [SQL Server Tutorials].[dbo].[Select Into]
FROM [SQLTEST].[dbo].[Employee] EMP
INNER JOIN
[SQLTEST].[dbo].[Department] DEPT ON
EMP.id = DEPT.id

SQL Execution Message

Messages
-------
(8 rows(s) affected)

Let us see whether the table with data in destination database is created or not

SQL SELECT INTO Statement 9