SQL INSERT INTO SELECT

The SQL Server INSERT INTO SELECT Statement can be used to insert data from one (source) table to another (target or destination) table. We can say it is the famous CCP (copy and paste) approach. It is beneficial to back up the existing tables before performing large operations or populating tables with sample data for analysis purposes.

The SQL INSERT INTO SELECT Statement is a combination of two parts. The SELECT statement selects the specified columns from the source table(s). Next, the INSERT INTO clause will insert those records into the destination table. Although it transfers all the rows from the source to the destination, we can use the WHERE clause to filter the source data or the TOP clause to select only the top N records. The order of the execution is FROM -> SELECT -> INSERT INTO.

If there are existing tables in the destination, then use the SQL Server INSERT INTO SELECT Statement to load the specified data into a destination. If no existing table exists, use the SELECT INTO statement.

SQL INSERT INTO SELECT Syntax

The syntax of the INSERT INTO SELECT Statement is as shown below.

INSERT INTO [Destination]
SELECT * FROM Source

If the table structure of both source and destination is the same, use the above SQL INSERT INTO SELECT Statement. However, if the source table has a larger number of columns and we only need a few columns in the destination, use the query below.

INSERT INTO [Destination]
SELECT [Column Names]
FROM Source

The above syntax only works if the Destination Table has the same number of columns mentioned in the SELECT statement and in the same order as the Source.

The most useful and error-free SQL INSERT INTO SELECT statement syntax that we use in real-time is shown below. It has more control over which columns to choose, the order of the columns, and how many are needed, among other things.

INSERT INTO [DestinationTable] (Column1, Column2,.., ColumnN)
SELECT Column1, Column2,.., ColumnN
FROM Source
WHERE Condition --This is optional
  • DestinationTable: It is the already existing table where we want to copy the source data. Please provide the fully qualified name along with the column names.
  • Columns (Column1, Column2,…, ColumnN): We must choose the required number of columns from the SQL Server Source and Destination tables. It may be one or more. Here, the number of columns mentioned in the SQL INSERT INTO Destination should match the columns placed in the SELECT statement.
  • Source: It is a table from which we copy the data to the destination. It can be a single table or multiple tables present in the Database. Use JOINS to join multiple tables.
  • WHERE: It is optional. Use this WHERE clause to filter or select a portion of data from the source table and copy it to the destination.
  • TOP: It is optional. Use to insert the top N rows or top percentage rows.

NOTE: There is no need to match the column name. However, the numbers and the data type should match or be implicitly convertible.

To demonstrate the SQL Server INSERT INTO SELECT Statement, we use the AdventureWorksDW2022 database. Within the database, we will create a few random tables to insert data from one table into another. First, we will create a new table using the DimProductCategory table definition. Once you execute the query below, it creates an empty table with zero records. Our task is to get the Data present in the Dim table to this newly created one.

CREATE TABLE [dbo].[ProductCategory](
	[ProductCategoryKey] [int] PRIMARY KEY NOT NULL,
	[ProductCategoryAlternateKey] [int] NULL,
	[EnglishProductCategoryName] [nvarchar](50) NOT NULL,
	[SpanishProductCategoryName] [nvarchar](50) NOT NULL,
	[FrenchProductCategoryName] [nvarchar](50) NOT NULL,
)

SQL INSERT INTO SELECT All Columns Example

If our task is to insert all the rows and columns from one table to another, you can use any one of the two methods. The first approach is as we mentioned in the syntax, we must specify the columns in both the source and destination tables. Here, we will choose all the columns in the DimProductCategory table and insert them into the [ProductCategory] within the same database.

INSERT INTO ProductCategory ([ProductCategoryKey]
 ,[ProductCategoryAlternateKey],[EnglishProductCategoryName]
 ,[SpanishProductCategoryName],[FrenchProductCategoryName])
SELECT [ProductCategoryKey]
      ,[ProductCategoryAlternateKey]
      ,[EnglishProductCategoryName]
      ,[SpanishProductCategoryName]
      ,[FrenchProductCategoryName]
FROM [DimProductCategory]

The above SQL INSERT INTO SELECT Statement query will select the specified five columns from the source and load them into the [ProductCategory] table.

ProductCategoryKey	ProductCategoryAlternateKey	EnglishProductCategoryName	SpanishProductCategoryName	FrenchProductCategoryName
1	1	Bikes	Bicicleta	Vélo
2	2	Components	Componente	Composant
3	3	Clothing	Prenda	Vêtements
4	4	Accessories	Accesorio	Accessoire

NOTE: Before writing a new query, please empty the table rows. For this, use the TRUNCATE TABLE statement.

TRUNCATE TABLE ProductCategory – Replace table name.

If we know that the number of columns in the source table exactly matches the columns in the SELECT Statement, then we can ignore the column names of the source and destination. It means we can leave the column names in the SQL INSERT INTO statement and use the asterisk (*) symbol in the SELECT statement. So, the above query can also be written as:

INSERT INTO ProductCategory 
SELECT * FROM [DimProductCategory]

The above query will work as long as the number of columns and the order of the columns of both the source and destination tables are identical. If there is any mismatch, it will throw an error.

TIP: It is not good practice to ignore the column names, so always provide the column names.

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

ProductCategoryKey	ProductCategoryAlternateKey	EnglishProductCategoryName	SpanishProductCategoryName	FrenchProductCategoryName
1	1	Bikes	Bicicleta	Vélo
2	2	Components	Componente	Composant
3	3	Clothing	Prenda	Vêtements
4	4	Accessories	Accesorio	Accessoire

Copy Selected Column Only

Suppose the table has an identity column with an auto increment of one. In such a case, we don’t have to insert any record into that column. In fact, it will throw an error if we do. To handle this situation, we must select a few number of records.

To demonstrate the same, drop the existing table and set the ProductCategoryKey as the identity column with a start value of 1 and an auto increment value of 1. Next, we removed two columns.

DROP TABLE [ProductCategory];
CREATE TABLE [dbo].[ProductCategory](
	[ProductCategoryKey] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[EnglishProductCategoryName] [nvarchar](50) NULL,
	[FrenchProductCategoryName] [nvarchar](50) NULL,
);

Our task is to SELECT all the rows in the English and French Category Names of the DimProductCategory table and SQL INSERT INTO the ProductCategory table.

INSERT INTO ProductCategory ([EnglishProductCategoryName],
    [FrenchProductCategoryName])
SELECT [EnglishProductCategoryName]
      ,[FrenchProductCategoryName]
FROM [DimProductCategory]
ProductCategoryKey	EnglishProductCategoryName	FrenchProductCategoryName
1	Bikes	Vélo
2	Components	Composant
3	Clothing	Vêtements
4	Accessories	Accessoire

SQL INSERT INTO SELECT Few Columns

When performing the INSERT INTO SELECT statement to transfer data, we must be careful with missing records. For instance, accidentally choosing fewer columns. 

In this example, we have chosen a few columns present in the DimProductCategory and put them into a destination table. Although we have the English and French category columns in the destination table, insert only EnglishProductCategoryName.

INSERT INTO ProductCategory ([EnglishProductCategoryName])
SELECT [EnglishProductCategoryName]
FROM [DimProductCategory]

From the below result set, you can see that the Data was inserted only for the EnglishProductCategoryName. The FrenchProductCategoryName column returns NULL values for the rows.

NOTE: If the French Category does not accept NULL values (NOT NULL), the above SQL INSERT INTO SELECT statement will throw an error.

Load with Default Values

Apart from inserting the records from an existing column, you can simply load the default or hardcoded values into the destination. For instance, the query below will load Unknown as the FrenchProductCategoryName for all four records.

INSERT INTO ProductCategory ([EnglishProductCategoryName],
[FrenchProductCategoryName] )
SELECT [EnglishProductCategoryName], 'Unknown' 
FROM [DimProductCategory]
ProductCategoryKey	EnglishProductCategoryName	FrenchProductCategoryName
1	Bikes	Unknown
2	Components	Unknown
3	Clothing	Unknown
4	Accessories	Unknown

Temporary Tables and Table Variables

We generally perform complex calculations on multiple tables within the stored procedure or the User-defined functions. When performing the same, we store the output in temporary tables or table variables. To do so, we may use the SQL INSERT INTO SELECT Statement.

To understand the same, use any of the above examples and add @, #, or ## as the prefix of the table name. Please refer to the Local and Global Temp Tables and Table Variable articles.

CREATE TABLE #ProductCategory(….);
Or
DELCARE @ProductCategory TABLE (….);

INSERT INTO #ProductCategory (…)
SELECT * FROM [DimProductCategory]

SQL INSERT INTO SELECT Statement with JOIN Clause

In all our previous examples, we used a single table to extract data from and load into a source table. However, there are situations where we have to use multiple tables as a source, and the combined data will load into the destination table. In such a situation, we must use the JOIN clause to combine two or more tables.

To demonstrate the SQL INSERT INTO SELECT Statement with a JOIN clause, we will create a new table with four columns.

CREATE TABLE [dbo].[TerritorySales](
	[SalesTerritoryGroup] [nvarchar](50) NULL,
	[Orders] [int] NULL,
	[ProductCost] [money] NULL,
	[Sales] [money] NULL
) ON [PRIMARY]
GO

If you observe the above table definition, it requires the Territory group information and their sales information. In our AdventureWorksDW database, the Territory information is stored in DimSalesTerritory and Sales information in the FactInternetSales table. So, to insert data into our newly created table, we must JOIN those two tables.

The following query will perform an INNER JOIN on those two tables and insert the records into the TerritorySales table.

INSERT INTO [TerritorySales]([SalesTerritoryGroup]
      ,[Orders],[ProductCost],[Sales])
SELECT st.[SalesTerritoryGroup],f.OrderQuantity
    ,f.TotalProductCost,f.SalesAmount
FROM [DimSalesTerritory] st JOIN FactInternetSales f 
ON st.[SalesTerritoryKey] = f.[SalesTerritoryKey]
(60398 rows affected)

As the above statement will load 60398 rows into the table, it’s impossible to show the result set on this page. To make it simple and explain the aggregations, we use the query below.

The statement below will perform the aggregations on numeric values (Orders, Sales, and Product Cost) and group the joined table by SalesTerritoryGroup. Next, insert them into the table.

TIP: It is helpful when creating a table specifically for higher-level reporting.

INSERT INTO [TerritorySales]([SalesTerritoryGroup]
      ,[Orders],[ProductCost],[Sales])
SELECT st.[SalesTerritoryGroup]
    ,SUM(f.OrderQuantity) 
    ,SUM(f.TotalProductCost) 
    ,SUM(f.SalesAmount)
FROM [DimSalesTerritory] st JOIN FactInternetSales f 
ON st.[SalesTerritoryKey] = f.[SalesTerritoryKey]
GROUP BY st.[SalesTerritoryGroup]

SQL INSERT INTO SELECT DISTINCT Statement

To insert a distinct or unique combination of records, use the INSERT INTO SELECT Statement with the DISTINCT keyword.

The query below is the same as the first example in the JOIN clause. However, we added the DISTINCT keyword. As we mentioned earlier, the above query will generate 60398 rows and load them into a target table. After adding the DISTINCT keyword, it will transfer 135 rows only. It means there are 135 combinations for those four columns.

INSERT INTO [TerritorySales]([SalesTerritoryGroup]
      ,[Orders],[ProductCost],[Sales])
SELECT DISTINCT st.[SalesTerritoryGroup]
    ,f.OrderQuantity, f.TotalProductCost,f.SalesAmount
FROM [DimSalesTerritory] st JOIN FactInternetSales f 
ON st.[SalesTerritoryKey] = f.[SalesTerritoryKey]
ORDER BY st.[SalesTerritoryGroup]
(135 rows affected)

SQL INSERT INTO SELECT Statement with WHERE Clause

Instead of inserting all the records in one or more tables into the destination, you can use the WHERE Clause to restrict the rows. For instance, copying sales data whose order date is above 2025-01-01, one year old data, the last five years, etc.

This example uses the LIKE operator inside the WHERE clause to insert category information where the EnglishProductCategoryName does not start with C.

INSERT INTO ProductCategory ([EnglishProductCategoryName],
	[FrenchProductCategoryName])
SELECT [EnglishProductCategoryName],[FrenchProductCategoryName]
FROM [DimProductCategory]
WHERE EnglishProductCategoryName NOT LIKE 'C%'
ProductCategoryKey	EnglishProductCategoryName	FrenchProductCategoryName
1	Bikes	Vélo
2	Accessories	Accessoire

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

Similar to the above, we can also use the SQL INSERT INTO SELECT Statement with a WHERE clause to filter the data from multiple tables. The equerry below inserts the records whose Territory region is not Pacific.

INSERT INTO [TerritorySales]([SalesTerritoryGroup]
      ,[Orders],[ProductCost],[Sales])
SELECT st.[SalesTerritoryGroup]
    ,SUM(f.OrderQuantity) 
    ,SUM(f.TotalProductCost) 
    ,SUM(f.SalesAmount)
FROM [DimSalesTerritory] st JOIN FactInternetSales f 
ON st.[SalesTerritoryKey] = f.[SalesTerritoryKey]
WHERE st.[SalesTerritoryGroup] != 'Pacific'
GROUP BY st.[SalesTerritoryGroup]
SalesTerritoryGroup	Orders	ProductCost	Sales
Europe	18089	5265915.9986	8930042.2634
North America	28964	6636732.069	11367634.3729

SQL INSERT INTO SELECT TOP Clause

As we mentioned in the syntax section, we can use the TOP clause (optional) within the INSERT INTO SELECT to choose the TOP N rows. There are situations where we need to insert the TOP N rows based on the HireDate, OrderDate, etc. In such a situation, we can use the INSERT INTO SELECT statement along with the TOP clause.

Let me create a new table (Employee) with five columns inside a Test database. As you can see, it is a simple table, and we use the DimEmployee table in the AdventureWorksDW database to fill the data.

CREATE TABLE [dbo].[Employee](
	[EmployeeKey] [int] NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[Title] [nvarchar](50) NULL
)

The DimEmployee table contains 31 columns and 296 rows, whereas the Employee table has only five columns. For some reason, we need the first four records from the DimEmployee table to be inserted into the Employee table. To do so, we use the SQL INSERT INTO SELECT statement with the TOP (4) clause.

INSERT INTO [Test].[dbo].[Employee] ( 
    [EmployeeKey],[FirstName],[LastName]
      ,[MiddleName],[Title]
 )
SELECT TOP (4) [EmployeeKey],[FirstName],[LastName]
      ,[MiddleName],[Title]
FROM [AdventureWorksDW2022].[dbo].[DimEmployee]
SQL INSERT INTO SELECT Statement

NOTE: If you observe the above query, we used the Database Name, Schema Name, and the Table name inside the SQL INSERT INTO SELECT statement. It is very important when transferring data from one database to another. If the process is within the same database, you can ignore the database name.

Insert the Top Percent of Rows

Instead of using the absolute number, such as TOP N, you can use the percentage of rows. For instance, loading the top 10 per cent of employees according to their performance. Before writing the actual query, let me delete all records from the table.

TRUNCATE TABLE [Employee]

The query below inserts the TOP 2 percent of rows from the DimEmployee into the Employee table.

INSERT INTO [Test].[dbo].[Employee] ( 
    [EmployeeKey],[FirstName],[LastName]
      ,[MiddleName],[Title]
 )
SELECT TOP (2) PERCENT [EmployeeKey],[FirstName]
      ,[LastName],[MiddleName],[Title]
FROM [AdventureWorksDW2022].[dbo].[DimEmployee]
EmployeeKey	FirstName	LastName	MiddleName	Title
1	Guy	Gilbert	R	Production Technician - WC60
2	Kevin	Brown	F	Marketing Assistant
3	Roberto	Tamburello	NULL	Engineering Manager
4	Rob	Walters	NULL	Senior Tool Designer
5	Rob	Walters	NULL	Senior Tool Designer
6	Thierry	D'Hers	B	Tool Designer

NOTE: The TOP N or PERCENT of rows always depends upon the table data. If you use the ORDER BY clause, the data will be sorted according to the ORDER BY clause column, and then the TOP clause selects the records from it.

Handling NULL Values in SQL INSERT INTO SELECT Statement

When transferring data from one table to another, you can leave the NULL values as they are or handle them as per the requirement. If you observe the above result set, you can see there are a few columns where the MiddleName field is NULL. To handle them, use the COALESCE function.

Use the query below to delete all rows from the table.

TRUNCATE TABLE [Employee]

The SQL INSERT INTO SELECT statement with the TOP clause will insert the first four rows from AdventureWorksDW to the Test database Employee table. When inserting, the COALESCE function checks whether there are any NULL values in the incoming MiddleName column. If so, replace those NULL values with TG.

INSERT INTO [Test].[dbo].[Employee] ( 
    [EmployeeKey],[FirstName],[LastName]
      ,[MiddleName],[Title]
 )
SELECT TOP (4) [EmployeeKey],[FirstName],[LastName]
      ,COALESCE([MiddleName], 'TG') AS MiddleName
      ,[Title]
FROM [AdventureWorksDW2022].[dbo].[DimEmployee]
EmployeeKey	FirstName	LastName	MiddleName	Title
1	Guy	Gilbert	R	Production Technician - WC60
2	Kevin	Brown	F	Marketing Assistant
3	Roberto	Tamburello	TG	Engineering Manager
4	Rob	Walters	TG	Senior Tool Designer

SQL INSERT INTO SELECT Best Practices

  1. Check whether the order and the data type of the columns in the INSERT INTO clause match the SELECT statement. If you try to insert a string text into an integer table, it will throw an error.
  2. Apart from the data type, the length also matters. If the target table accepts a maximum of 50 characters, and if you try to insert 100 characters?
  3. Always check whether the destination table already exists or not. Otherwise, first create a new table and then perform the SQL INSERT INTO SELECT statement.
  4. While loading a large data set from source to destination, performance will be impacted. To improve it, use filters or process the data in batches of 50,000 or 100,000 rows at one time.
  5. When inserting data into the destination table, please check whether there are any constraint violations or triggers. For instance, loading NULLs into NOT NULL columns, duplicates into a UNIQUE column, etc.
  6. Use the WHERE clause to filter the data.
  7. Use TRANSACTIONS to avoid errors.
Categories SQL

Comments are closed.