SQL Server INSERT INTO SELECT Query

How to insert data table using insert into queries called from a SQL Server Select?

There are 3 solutions to run an INSERT INTO query within a SQL SELECT. 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 and a UNION ALL to group several rows into a single insertion statement. And last but not least, the third one uses a SELECT that retrieves data from another table, in this specific example we are using the same table.

Create the sample SQL table before the INSERT INTO SELECT
Conclusion about simple queries to insert data from a Select

Create the sample SQL table before the INSERT INTO SELECT

Firstly, of course, to test our 3 insert into example queries, we need a work table. So just copy and paste the script into SQL Server Management Studio. Once working you need to adapt the queries to your specific needs before running them. To create the empty Customers table, run the queries below which is the target table in our 3 following examples.

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 NAME column.

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

1. Use the INSERT INTO SELECT query with multiple queries

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';

2. SQL Server INSERT INTO with multiple selection and a UNION ALL

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

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';

3. SQL Server INSERT INTO From Another Table’s SELECT

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.

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

Finally, you can check all rows, i.e. 6 rows already present from steps 1 and 2 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;

Conclusion about simple queries to insert data from a Select

To conclude, multiple technical solutions exist in SQL Server to enrich tables and to copy data, using hard coded insert statements or select queries from other tables.

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.