What is INSERT … SELECT

In addition to specifying a literal set of values in an INSERT statement, T-SQL also supports using the results of other operations to provide values for INSERT. You can use the results of a SELECT statement or the output of a stored procedure to supply the values for the INSERT statement.

To use the INSERT with a nested SELECT, build a SELECT statement to replace the VALUES clause. With this form, called INSERT SELECT, you can insert the set of rows returned by a SELECT query into a destination table. The use of INSERT SELECT presents the same considerations as INSERT VALUES:

• You may optionally specify a column list following the table name.
• You must provide column values or DEFAULT, or NULL, for each column.

The following syntax illustrates the use of INSERT SELECT:

SQLCopy

INSERT [INTO] <table or view> [(column_list)]
SELECT <column_list> FROM <table_list>...;

 Note

Result sets from stored procedures (or even dynamic batches) may also be used as input to an INSERT statement. This form of INSERT, called INSERT EXEC, is conceptually similar to INSERT SELECT and will present the same considerations. However, stored procedures can return multiple result sets, so extra care is needed.

The following example inserts multiple rows for a new promotion named Get Framed by retrieving the model ID and model name from the Production.ProductModel, table for every model that contains “frame” in its name.

SQLCopy

INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount, Notes)
SELECT DISTINCT 'Get Framed', m.ProductModelID, 0.1, '10% off ' + m.Name
FROM Production.ProductModel AS m
WHERE m.Name LIKE '%frame%';

Unlike a subquery, the nested SELECT used with an INSERT isn’t enclosed in parentheses.