INSERT INTO SELECT vs SELECT INTO In SQL Server

Introduction

In this tutorial, I am going to explain the difference between INSERT INTO SELECT and SELECT INTO In SQL Server. This detailed article will cover the following topics,

1. Introduction
2. INSERT INTO SELECT Statement In SQL Server
3. SELECT INTO Statement In SQL Server
4. Difference Between INSERT INTO SELECT and SELECT INTO In SQL Server
5. Conclusion

 First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE OnkarSharma_Vatsa
PRINT 'New Database ''OnkarSharma_Vatsa'' Created'
GO

USE [OnkarSharma_Vatsa]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL UNIQUE,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[Restaurant](
	RestaurantId INT IDENTITY (51, 1),
	RestaurantName VARCHAR(MAX) NOT NULL,
	Email VARCHAR(100),
	City VARCHAR(100),
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL,
	PRIMARY KEY(RestaurantId)
);

CREATE TABLE [dbo].[tbl_Orders] (
	OrderId INT IDENTITY (108, 1) PRIMARY KEY,
	FoodieID INT,
	OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATE NOT NULL,
	ShippedDate DATE,
	RestaurantId INT NOT NULL,
);

CREATE TABLE [dbo].[tbl_OrderItems](
	OrderId INT NOT NULL,
	ItemId INT,
	MenuId INT NOT NULL,
	Quantity INT NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	Discount DECIMAL(5, 2) NOT NULL DEFAULT 0,
	PRIMARY KEY (ItemId)
);

CREATE TABLE [dbo].[tbl_Menu] (
	MenuId INT IDENTITY (81, 1) PRIMARY KEY,
	FoodCategoryID INT NOT NULL,
	FoodName VARCHAR (255) NOT NULL,
	TypeofFood VARCHAR (100) NOT NULL,
	Price DECIMAL(6, 2) NOT NULL
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_Vatsa..Employee

2) To get the data from the "Restaurant" table, use the following query.

SELECT * FROM OnkarSharma_Vatsa..Restaurant

INSERT INTO SELECT Statement In SQL Server

INSERT INTO SELECT statement in SQL Server is used to copy the data from the source table and insert it into the target table. But, before copying the data, the target table must exist in the database.

Key Points

• The INSERT INTO SELECT statement requires the target table to exist in the database before data can be transferred from the source table to the target table.
• The data types in the source and target tables must match for the INSERT INTO SELECT statement to work.

Syntax

INSERT [TOP (expression [PERCENT])]
INTO <target_table> (<column_name>)
SELECT * FROM <source_table>
[WHERE condition]

Note:

The TOP clause allows you to specify the number of rows returned by the query to insert into the target table. If you use the PERCENT option, the statement will insert a percentage of the rows instead.

Examples

The examples in this section demonstrate the functionality of the INSERT INTO SELECT Statement. Let's see.

1) Insert all data [all columns] from the source table to the destination table

Let's create a table named "[dbo].[Restaurant_backup_12112021]" for demonstration.

CREATE TABLE [dbo].[Restaurant_backup_12112021](
	RestaurantId INT,
	RestaurantName VARCHAR(MAX) NOT NULL,
	Email VARCHAR(100),
	City VARCHAR(100),
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL,
);

The following query will insert all the addresses from "[dbo].[Restaurant]" table into the "[dbo].[Restaurant_backup_12112021]" table.

INSERT INTO [dbo].[Restaurant_backup_12112021]
SELECT * 
FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[Restaurant_backup_12112021]

2) Insert data of specific columns from the source table to the destination table

Let’s create a table named "[dbo].[tbl_Restaurant_Address]" for demonstration.

IF OBJECT_ID(N'[dbo].[tbl_Restaurant_Address]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_Restaurant_Address]
END

CREATE TABLE [dbo].[tbl_Restaurant_Address](
	AddressId INT IDENTITY (51, 1) PRIMARY KEY,
	RestaurantName VARCHAR(MAX) NOT NULL,
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL
);

The following query will insert all addresses from "[dbo].[Restaurant]" table into the "[dbo].[tbl_Restaurant_Address]" table.

INSERT INTO [dbo].[tbl_Restaurant_Address] (RestaurantName, Zipcode, State, Country) 
SELECT RestaurantName, Zipcode, State, Country
FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_Restaurant_Address]

3) Insert rows with WHERE condition

Let’s create a table named "tbl_Restaurant_Address" for demonstration.

IF OBJECT_ID(N'[dbo].[tbl_Restaurant_Address]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_Restaurant_Address]
END

CREATE TABLE [dbo].[tbl_Restaurant_Address](
	AddressId INT IDENTITY (51, 1) PRIMARY KEY,
	RestaurantName VARCHAR(MAX) NOT NULL,
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL
);

 The following query will insert specific addresses from the "[dbo].[Restaurant]" table into the "[dbo].[tbl_Restaurant_Address]" table.

INSERT INTO [dbo].[tbl_Restaurant_Address] (RestaurantName, Zipcode, State, Country) 
SELECT RestaurantName, Zipcode, State, Country
FROM [dbo].[Restaurant]
WHERE State IN ('UP', 'Delhi')

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_Restaurant_Address]

4) Insert top N of rows

A) Top N of rows

Let’s create a table named "tbl_Restaurant_Address" for demonstration.

IF OBJECT_ID(N'[dbo].[tbl_Restaurant_Address]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_Restaurant_Address]
END

CREATE TABLE [dbo].[tbl_Restaurant_Address](
	AddressId INT IDENTITY (51, 1) PRIMARY KEY,
	RestaurantName VARCHAR(MAX) NOT NULL,
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL
);

The following query will insert top 10 addresses from the "[dbo].[Restaurant]" table into the "[dbo].[tbl_Restaurant_Address]" table.

INSERT TOP (10) 
INTO [dbo].[tbl_Restaurant_Address] (RestaurantName, Zipcode, State, Country) 
SELECT RestaurantName, Zipcode, State, Country
FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_Restaurant_Address]

B) Top N PERCENT of rows

Let’s create a table named "tbl_Restaurant_Address" for demonstration.

IF OBJECT_ID(N'[dbo].[tbl_Restaurant_Address]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_Restaurant_Address]
END

CREATE TABLE [dbo].[tbl_Restaurant_Address](
	AddressId INT IDENTITY (51, 1) PRIMARY KEY,
	RestaurantName VARCHAR(MAX) NOT NULL,
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL
);

The following query will insert top 10 PERCENT addresses from the "[dbo].[Restaurant]" table into the "[dbo].[tbl_Restaurant_Address]" table.

INSERT TOP (10) PERCENT
INTO [dbo].[tbl_Restaurant_Address] (RestaurantName, Zipcode, State, Country) 
SELECT RestaurantName, Zipcode, State, Country
FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_Restaurant_Address]

5) WITH JOIN Clause: To get data from multiple tables 

Let’s create a table named "[dbo].[tbl_OrderDetails]" for demonstration.

IF OBJECT_ID(N'[dbo].[tbl_OrderDetails]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_OrderDetails]
END

CREATE TABLE [dbo].[tbl_OrderDetails](
	OrderDetailId INT IDENTITY (51, 1),
	RestaurantName VARCHAR(MAX) NOT NULL,
	Email VARCHAR(100),
	City VARCHAR(100),
	OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATE NOT NULL,
	ShippedDate DATE,
	Quantity INT NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	FoodName VARCHAR (255) NOT NULL
);

The following query will insert data from multiple tables into the "[dbo].[tbl_OrderDetails]" table.

INSERT INTO [tbl_OrderDetails]
    SELECT DISTINCT 
    R.RestaurantName, 
    R.Email, 
    R.City, 
    O.OrderStatus, 
    O.OrderDate, 
    O.ShippedDate, 
    OI.Quantity, 
    OI.Price, 
    M.FoodName
FROM Restaurant R
INNER JOIN tbl_Orders O ON R.RestaurantId = O.RestaurantId
INNER JOIN tbl_OrderItems OI ON O.OrderId = OI.OrderId
INNER JOIN tbl_Menu M ON OI.MenuId = M.MenuId

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_OrderDetails]

6) WITH a table variable

--Let’s create a table named "@tbl_Restaurant_Address" for demonstration.
	DECLARE @tbl_Restaurant_Address TABLE (
		AddressId INT IDENTITY (51, 1) PRIMARY KEY,
		RestaurantName VARCHAR(MAX) NOT NULL,
		Zipcode VARCHAR(6) NOT NULL,
		State VARCHAR(100) NOT NULL,
		Country VARCHAR(100) NOT NULL
	);

--The following query will insert all addresses from the [dbo].[Restaurant] table into the @tbl_Restaurant_Address table variable.
	INSERT INTO @tbl_Restaurant_Address (RestaurantName, Zipcode, State, Country) 
	SELECT RestaurantName, Zipcode, State, Country
	FROM [dbo].[Restaurant]

--To verify the insertion, use the following query.
	SELECT * FROM @tbl_Restaurant_Address

7) WITH a temporary table

--Let’s create a table named "#tbl_Restaurant_Address" for demonstration.
	CREATE TABLE #tbl_Restaurant_Address(
		AddressId INT IDENTITY (51, 1) PRIMARY KEY,
		RestaurantName VARCHAR(MAX) NOT NULL,
		Zipcode VARCHAR(6) NOT NULL,
		State VARCHAR(100) NOT NULL,
		Country VARCHAR(100) NOT NULL
	);

--The following query will insert all addresses from the [dbo].[Restaurant] table into the #tbl_Restaurant_Address table.
	INSERT INTO #tbl_Restaurant_Address (RestaurantName, Zipcode, State, Country) 
	SELECT RestaurantName, Zipcode, State, Country
	FROM [dbo].[Restaurant]

--To verify the insertion, use the following query.
	SELECT * FROM #tbl_Restaurant_Address

SELECT INTO Statement In SQL Server

The SELECT INTO statement in SQL Server is used to copy data from one table to a new table.

Key Points

• The new table does not need to exist in the database to transfer data from the source table.
• A new table with the structure is automatically created while executing this command.
• The new table will be created with the column names and types defined in the source table.
• Users can also create new column names using the "AS" clause.
• SELECT INTO statement cannot be used to insert data into an existing table.
• SELECT INTO statement does not copy constraints such as primary key and indexes from the source_table to the new_table.

Syntax

SELECT <select_list>
INTO <new_table>
FROM <source_table>
[WHERE condition]

Examples

The examples in this section demonstrate the functionality of the SELECT INTO Statement. Let's see.

1) Insert all data [all columns] from the source table into a new table

The following query will insert all data from the "[dbo].[Employee]" table into the new "[dbo].[Employee_bak_12132021]" table.

SELECT * INTO [dbo].[Employee_bak_12132021]
FROM [dbo].[Employee]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[Employee_bak_12132021]

2) Insert data of specific columns from the source table into a new table

The following query will insert data of specific columns from the "[dbo].[Restaurant]" table into the new "[dbo].[tbl_RestaurantAddress]" table.

SELECT 
	RestaurantName, 
	Zipcode, 
	State, 
	Country 
INTO [dbo].[tbl_RestaurantAddress]
FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_RestaurantAddress]

3) Insert rows with WHERE condition

The following query will insert specific addresses from the "[dbo].[Restaurant]" table into the new "[dbo].[tbl_RestaurantAddress]" table.

IF OBJECT_ID(N'[dbo].[tbl_RestaurantAddress]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[tbl_RestaurantAddress]
END

SELECT 
	RestaurantName, 
	Zipcode, 
	State, 
	Country 
INTO [dbo].[tbl_RestaurantAddress]
FROM [dbo].[Restaurant]
WHERE State IN ('UP', 'Delhi')

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_RestaurantAddress]

4) WITH JOIN Clause: To get data from multiple tables 

The following statement inserts data from multiple tables into the new "[tbl_Order_Details]" table.

SELECT DISTINCT 
    R.RestaurantName, 
    R.Email, 
    R.City, 
    O.OrderStatus, 
    O.OrderDate, 
    O.ShippedDate, 
    OI.Quantity, 
    OI.Price, 
    M.FoodName
INTO [tbl_Order_Details]
FROM Restaurant R
INNER JOIN tbl_Orders O ON R.RestaurantId = O.RestaurantId
INNER JOIN tbl_OrderItems OI ON O.OrderId = OI.OrderId
INNER JOIN tbl_Menu M ON OI.MenuId = M.MenuId

To verify the insertion, use the following query.

SELECT * FROM [dbo].[tbl_Order_Details]	

5) To copy table across databases

--First, let’s create a database named "TestDatabase" for demonstration.
	IF (DB_ID('TestingDatabase') IS NOT NULL)
	BEGIN
		DROP DATABASE TestingDatabase;
	END
	CREATE DATABASE TestingDatabase;
	PRINT 'New Database ''TestingDatabase'' Created'

--The following query will insert all data from the [dbo].[Restaurant] table into the [dbo].[tbl_RestaurantAddress] table.
	SELECT 
		RestaurantName, 
		Zipcode, 
		State, 
		Country 
	INTO TestingDatabase.[dbo].[tbl_RestaurantAddress]
	--INTO TestDatabase..tbl_RestaurantAddress
	FROM [dbo].[Restaurant]

To verify the insertion, use the following query.

SELECT * FROM TestingDatabase..tbl_RestaurantAddress

6) WITH a temporary table

--The following query will insert all addresses from the [dbo].[Restaurant] table into the #tbl_Restaurant_Addresses table.
	SELECT RestaurantName, Zipcode, State, Country
	INTO #tbl_Restaurant_Addresses 
	FROM [dbo].[Restaurant]

--To verify the insertion, use the following query.
	SELECT * FROM #tbl_Restaurant_Addresses

Bonus Point

"SELECT INTO" can also be used to create a new (empty) table using the schema of the source table. To do this, all you need to do is add a WHERE clause that will not copy the data to the new table.

Syntax

SELECT * INTO <new_table>
FROM <source_table>
WHERE 1 = 0;

Example

SELECT * INTO New_EmployeeTable
FROM Employee
WHERE 1 = 0;

(0 rows affected) will show on the screen as an output message.

Difference Between INSERT INTO SELECT and SELECT INTO In SQL Server

Now, let's look at the difference between INSERT INTO SELECT and SELECT INTO In SQL Server.

S.No.	Key Points	INSERT INTO SELECT	SELECT INTO
1	Definition	INSERT INTO SELECT statement in SQL Server is used to copy data from the source table and insert it into the destination table.	The SELECT INTO statement in SQL Server is used to copy data from one (source) table to a new table.
2	Working	INSERT INTO SELECT requires the destination table to be pre-defined.	SELECT INTO does not require the destination (new) table to be pre-defined. Because it creates the table as part of the statement.
3	WHERE Condition?	Yes, WHERE Clause can be used with INSERT INTO SELECT statement to filter the records.	Yes, WHERE Clause can be used with SELECT INTO statement to filter the records.
4	Key Constraints	Defined during the creation of the target table.	Key constraints when creating a new table will not copy to the new table.
5	Speed	INSERT INTO SELECT is slower than SELECT INTO.	SELECT INTO is faster than INSERT INTO SELECT.

See you in the next article, till then take care and be happy learning.

You may also visit my other articles,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• Different Ways To Handle NULL In SQL Server
• DDL Triggers In SQL Server
• IIF Function In SQL Server

Conclusion

In this article, we have discussed the difference between INSERT INTO SELECT and SELECT INTO In SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.