SQL Server Insert Into from a SELECT Query

With SQL Server, how to load data into a table using an insert into query directly from a 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 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 examples, we are using the same source table.

Create the sample SQL table before the INSERT INTO SELECT
Solution 1 : Use an INSERT INTO SELECT type query multiple times
Solution 2 : SQL Server INSERT INTO with multiple select and UNION ALL
Solution 3 : SQL Server INSERT INTO From Another Table’s SELECT
Conclusion on simple solutions to insert data from Selects

Create the sample SQL table before the INSERT INTO SELECT

The very first step, of course, is to create a test table. We’ll use it in our 3 example queries.

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 TABLE [dbo].[Customers] (
	[Id_Customer] int IDENTITY(1,1),
	[Name] nvarchar(20) UNIQUE,
	[City] nvarchar(20)
);

Solution 1 : Use an INSERT INTO SELECT type query multiple times

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

Solution 2 : SQL Server INSERT INTO with multiple select and UNION ALL

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 3 : SQL Server INSERT INTO From Another Table’s SELECT

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 simple solutions to insert data from Selects

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.

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.

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.