We'll use the SELECT statement in conjunction with the INSERT statement to make this as easy as possible. Normally, you 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. You 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. We can actually replace the VALUES clause with a SELECT statement that will return a set of records. Suppose we 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
WHERE State = 'CA'
This will take the 15 records with State='CA' and load them into the table california_authors. You can use any type of SELECT statement here. It just has to return a record set that matches the columns in the INSERT statement. It number of columns and datatypes must match (or be implicitly convertable).
You can also execute a stored procedure that returns a record set using the EXEC command in place of the SELECT statement.