Using SELECT to INSERT records

By Bill Graziano on 15 August 2000 | 28 Comments | Tags: INSERT

Edwin writes "Ok, this may be simple to some, but it sure is a puzzle to me. I want to move a set of data from one table to another table with a similar structure. What I figure was a shady solution: Selecting the data from the source table, then opening the other table and using a loop to populate the destination table. Question: Is there a way I can use just ONE insert statement to do all this? Sort of like incorporating the select statement into the insert statement? If there is one, then please give me some example code." I certainly can do this in one statement (but looks like two). (This article has been updated through SQL Server 2005.)

I'll use the SELECT statement in conjunction with the INSERT statement to make this as easy as possible. Normally, I would code an INSERT statement something like this (using the pubs database):

INSERT authors (au_id, au_lname, au_fname, contract)
VALUES ('123-45-6789', 'Gates', 'Bill', 1)

This will insert one row into the authors table. I could write a program to loop through a set of records and insert them one at a time into another table. SQL Server is designed for set processing. It is optimized to handle groups or sets of records. I can actually replace the VALUES clause with a SELECT statement that will return a set of records. Suppose I have a table called CALIFORNIA_AUTHORS and we want to populate it with the ID and names of the authors from California. The statement would look something like this:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

This will take the 15 records with State='CA' and load them into the table CALIFORNIA_AUTHORS. I can use any type of SELECT statement here. It just has to return a record set that matches the columns in the INSERT statement. The number of columns and their data types must match (or be implicitly convertible). I can also execute a stored procedure that returns a record set using the EXEC command in place of the SELECT statement.

Discuss this article: 28 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Fast CSV Import in PowerShell to SQL Server (18 March 2014)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Using Views to Enforce Business Rules (9 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Anticipating Primary Key Violations (23 November 2003)

Using EXISTS (12 October 2003)

Other Recent Forum Posts

Shrink DB File by Increment to Target Free Space (6 Replies)

Getting current quarter & 4 previous quarters data (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

?????? ??????????? ?????? 2017 (0 Replies)

WITH EXECUTE_AS not working for sproc. (9 Replies)

Data loss during sql cluster failover (3 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -