mssqltips logo

INSERT INTO SQL Server table with SELECT command



By:

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

Last Update: 9/16/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, December 09, 2016 - 2:26:48 AM - ElyasFeyzollahi Back To Top

Hi how to can i write this code?

Insert into tbl1(col1,col2,col3) 

Select col1,col2 from tbl2 

,'data for col3'

I want insert col3 with out this select and insert manualy

What do i do?



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools