Insert into from a select query in SQL Server

How to write an Insert into query from a Select with SQL Server? There are three solutions for running an INSERT INTO query with a SELECT in SQL Server. The first solution is to insert the data with a SELECT query that returns the information in hard copy. The second solution is to use a SELECT and a UNION ALL to group several rows into a single INSERT statement. The third one uses a SELECT that retrieves data from another table, in this case we are using the same table.

Three examples of Insert into queries from a select statement with SQL Server

Firstly, to use the three insert into examples, copy and paste them into SQL Server Management Studio. Then adapt the queries to your specific needs before running them.

First, run the query to create the CUSTOMERS table, which is the target table for our examples.

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

For further examples of SQL Server with the clients table, click here.

To start, create the CUSTOMERS table with the NAME column declared as unique. Then the same customer name is unique and not duplicated. The Id_Customer customer number is auto-incremented to have a rolling list of customer numbers.

CREATE TABLE [dbo].[Customers] (
[Id_Customer] int IDENTITY(1,1),
[Name] nvarchar(20) UNIQUE,
[City] nvarchar(20)
);

First solution: Insert the data with a simple SELECT query to provide the data to be inserted

The first solution is the simplest and easiest one to implement. Especially if you are familiar with editing several rows in columns, with Excel for example.

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

Second solution: Inserting the data with a SELECT query and a UNION ALL that groups the 3 rows.

This solution also makes it easy to read the inserted data because it comes from a simple selection combined with row unions.

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

Third solution: INSERT INTO from the same table to double the existing rows

Add the suffix “-Junior” to differentiate the rows. In fact, to allow insertion without error because of the UNIQUE constraint added on the NAME column.

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

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

Finally, check all rows, i.e. six rows already present and six new rows of data inserted.


The Customers lines inserted into the target table are visible from a simple Select query.

SELECT * FROM dbo.Customers;

On the other hand, the T-SQL language also allows you to display the full list of tables available in a database.