Using SELECT to INSERT records

By Bill Graziano on 15 August 2000 | 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.

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

Operand data type bit is invalid for max operator (84m)

SSIS Script IN (2h)

REPORTING SERVICES ERROR: "Index was outside the bounds of the array" (4h)

Log shipping in SQL Server 2008 R2 (8h)

Compatibility level changed from SQL server 2005 90 to SQL server 2012 110. The select into query missing the SORT operation in insert execution plan (13h)

Help with CASE in a WHERE Clause (22h)

Return value based on value inside query (23h)

Combine results from different datasets on one Tablix in Report Builder 3.0 (2d)

- Advertisement -