SQL Server insert into from a select

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.

Then, 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 SQL Server queries to insert data into a table from a select statement

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];
GO

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’s 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 is to use the Insert query with a simple Select statement

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 is to Insert the data with a SELECT query and a UNION ALL that groups multiple 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 is to Insert data 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.