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′
,’Karon@Chunking4Sharks.com’
,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’, ‘Katie@QueenCrab.com’, 1,’2011-09-15′
UNION ALL
SELECT ‘Jessica’, ‘TastyTuna’, ‘555-333-3333’, ‘Jessica@TastyTuna.com’, 1,’2011-09-15′
UNION ALL
SELECT ‘Sharon’, ‘WellDoneSteak’, ‘666-333-3333’, ‘Sharon@OnlyBeef.com’, 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
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
