Copying records from one table to another

 

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 INDIA_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 india_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'UP'

This will take the 15 records with State='UP' and load them into the table INDIA_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.