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
