SQL Server Insert Into from a SELECT with 3 easy queries

With SQL Server, how to insert data into a table directly from a Select query ?

With the MS database SQL Server, Insert Into from a SELECT query is possible using multiple ways. Here we’ll cover 3 solutions.

The first solution is to insert directly the data with a SELECT query just after the INSERT INTO keyword. Then, the second solution is to use a SELECT with a UNION ALL to group several rows into a single insert statement. And last but not least, the third one uses a SELECT that retrieves data from another table.

In this article with different example query, we are using the same source table.

Create the sample SQL table before the INSERT INTO SELECT

The very first step, of course, is to create the sample table. To go aheaed, just copy and paste the script into SQL Server Management Studio. Of course you will have to adapt the queries to your specific needs before running them on your environement.

First simply reuse the logic and the example that suits you the most. To create the empty Customers table, run the queries below which is the target table.

Delete the Customers if it exists in your database.

Use a development or sandbox database of course!

IF EXISTS( 
	SELECT 1 FROM sys.objects 
	WHERE object_id = object_id(N'[dbo].[Customers]') 
		AND type in (N'U') )
DROP TABLE [dbo].[Customers];
GO

For further examples of SQL Server insert using a procedure this time, click here.

Create the Customers table using this code

The Id_Customer is auto incremented to have a rolling list of customer numbers. And the Name column is declared as unique, it means that you cannot enter 2 times the same name in this column.

In other words, it is not possible to have duplicates on the Customers Name column.

-- Create the Customers table with CustomerID column declared as NOT NULL
CREATE TABLE dbo.Customers (
	[CustomerID]   INT NOT NULL,
	[FirstName]    NVARCHAR(20),
	[LastName]     NVARCHAR(20) NULL,
	[City]         NVARCHAR(20) NULL,
	[Country]      NVARCHAR(50) NULL,
	CONSTRAINT [CustomersPrimaryKeyCustomerID] 
		PRIMARY KEY CLUSTERED ([CustomerID] ASC);
GO 

Solution 1 – Use a multiple INSERT INTO SELECT in SQL Server

The first DML (Data Manipulation Language) solution is the simplest and most common one to implement. Especially if you are familiar with editing several rows in columns, with Excel for example. And for limited number of lines. other wise you will need to genarate multiple independant insert statements.

-- Insert 4 different lines
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 1, 'Ali','Ahmed','Cairo','Egypt');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 2, 'Johnny','John','Toronto','Canada');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 3, 'Johnny','Doe','Mexico City','Mexico');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 4, 'Shu','Abbas','Paris','France');

Solution 2 – Use INSERT INTO SELECTs with UNION ALL in SQL Server

This solution also makes it easy to read the inserted data because it comes from a simple selection combined with Union All statements. Of course it can also comes from select queries that are not hard coded.

Empty the table using a TRUNCATE TABLE first.

INSERT INTO dbo.Customers ( Name, City )
	SELECT N'MR MAMMADOU', 'Zurich'   UNION ALL
	SELECT N'MR SERGEI', 'Ushuaia'    UNION ALL
	SELECT N'MR KARIM', 'Casablanca';

Solution 2 – SQL Server Insert Into from a Select from the same table

This time, let’s dd the suffix “-Junior” to differentiate the newly inserted rows from the previous ones. Indeed, we must do this to allow insertion without error, because of the Uniqueness constraint added on the Name column.

We simply insert from the same table, so the source and the target are the same unique table : Customers.

INSERT INTO dbo.Customers (Name, City)
	SELECT 	NAME + '-Junior', City
	FROM 	dbo.Customers;

Finally, you can check all rows, i.e. 3 rows already present from step 2 and 3 new rows of data inserted. The Customers lines inserted into the target table are visible using a simple Select query.

SELECT *
FROM   dbo.Customers;

Conclusion on how to use the SQL Server SELECT INTO Query to insert data

To conclude, different solutions exist in SQL Server to use the Insert Into from a Select statement. The goal is often to enrich tables and to copy data, using hard coded insert statements or select queries from other tables.

Of course those 3 examples use only 3 lines of data and are simple, on purpose. But when dealing with large amounts of data or complex transformations, those queries can be very useful.

So that’s it for the SQL Server Insert Into from a Select Query article! On a different topic, link to table storage, the T-SQL language also allows you to display the full list of the tables size and space available in a database.

How to Get the Size of all Tables in SQL Server ?