SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to insert the data into SQL Server tables. The SELECT INTO method first creates a new table and then it will insert the selected rows by the SELECT Statement into that new table.

TIP: If we haven’t created the destination table then SELECT INTO statement can be very useful to insert the data into the destination table. If you have an existing table then use INSERT INTO SELECT Statement.

SQL SELECT INTO Statement Syntax

The SELECT INTO Statement 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
• Source: One or more tables present in the Database. Use JOINS to join multiple tables.

In this article, we will show you, How to write SELECT INTO statement in SQL Server. From the below screenshot you can observe that We don’t have any Department or Employee table in the [SQL Server Tutorials] and Our task is to Select the Data present that tables in the [SQLTEST] database and insert into [SQL Server Tutorials] Database using SQL SELECT INTO Statement.

Data inside the Employee Table is

Data inside the Department Table is

SQL SELECT INTO Statement – Inserting All Columns

In this example, We are going to 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 asterisk so always provide the column names.

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

Above T-SQL query will create a new table called [Department] in the [SQL Server Tutorials] Database and insert all the records from the source data.

OUTPUT

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

SQL SELECT INTO Statement – Insert Few Columns

In this example, We are going to select a few Columns present in the Employee table and insert them into [SQL Server Tutorials] Database. Here we are going to 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 T-SQL query will create new table called [Employee] in the [SQL Server Tutorials] Database and insert [FirstName], [LastName] and [DepartID] columns from the source data where [DepartID] value is equal to 1.

OUTPUT

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

SQL SELECT INTO – Insert Data from Multiple Tables

In this example, We are going to select Columns present in both the Employee table and Department table then, insert them into [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

OUTPUT

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