From BOL

The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.

This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:

USE pubs

INSERT INTO MyBooks

SELECT title_id, title, type

FROM titles

WHERE type = 'mod_cook'

The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into