Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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?


Learn more about SQL Server tools