How to Insert Multiple Rows into a SQL Server Table from a Non-Scalar Subquery?

Inserting Values into a Table from a Non-Scalar Subquery

In SQL Server, inserting values into a table based on a subquery that returns multiple results can be challenging. Consider the following scenario:

You have two tables:

• article
• prices

You want to insert rows into the prices table with specific values for the group, id, and price columns, where the id values are derived from a subquery that selects matching IDs from the article table.

Incorrect Approach:

Trying to use a subquery with more than one result as the value for a column in an INSERT statement can result in an error, as demonstrated by the following incorrect SQL statement:

INSERT INTO prices (group, id, price) 
VALUES (7, (select articleId from article WHERE name LIKE 'ABC%'), 1.50);

Correct Solution:

To resolve this issue, use a SELECT statement to retrieve the desired values and insert them into the target table. Assign the constant values for the group and price columns directly within the SELECT statement:

insert into prices (group, id, price)
select 
    7, articleId, 1.50
from article where name like 'ABC%';

In this statement, the subquery returns the article IDs matching the specified name criteria, and the constant values 7 and 1.50 are explicitly assigned to the group and price columns, respectively.

The above is the detailed content of How to Insert Multiple Rows into a SQL Server Table from a Non-Scalar Subquery?. For more information, please follow other related articles on the PHP Chinese website!