INSERT INTO SQL Server table with SELECT command

By: Jeremy Kadlec

Overview

In some of our earlier examples we used the a SELECT statement to verify the results of the INSERT statements previously issued.  As you know, the INSERT command adds records to a table and the SELECT statement retrieves data from one or more tables.  Did you know that you can use a SELECT statement with an INSERT command to populate a table?  Let's take a look at three examples.

Explanation

In the first example, the INSERT command is used with static values from the SELECT command as shown below:

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
SELECT 'Karon'
 ,'Sharker'
 ,'333-333-3333'
 ,'[email protected]'
 ,1
 ,'2011-09-15';
GO

In the second example, the INSERT command is used with numerous static values with separate SELECT and UNION commands resulting in three records being inserted as shown below:

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
SELECT 'Katie', 'QueenCrab', '444-333-3333', '[email protected]', 1,'2011-09-15'
UNION ALL
SELECT 'Jessica', 'TastyTuna', '555-333-3333', '[email protected]', 1,'2011-09-15'
UNION ALL
SELECT 'Sharon', 'WellDoneSteak', '666-333-3333', '[email protected]', 1,'2011-09-15'
GO

In the third example, the INSERT command is used with a SELECT command accessing data from an archive table to populate the dbo.Customer table as shown below:

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
SELECT [FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate]
FROM Archive.dbo.Customers_OLD
GO