How to insert values ​​into a table using a subquery with multiple results?

Question

I really would appreciate your help.

Perhaps this is a fairly simple problem to solve - but I'm not the one who ...; -)

I have two tables in SQL Server:

• articles
• prices

Now I want to select a specific set of identifiers and insert some entries in the price table with these identifiers.

eg. (SQL does not work correctly)

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

SQL error -> subquery has more than 1 value

thanks for the help

Answer 1

Do you want to:

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

where you just encode constant fields.

Answer 2

Try the following:

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

Answer 3

 INSERT INTO prices (group, id, price) SELECT 7, articleId, 1.50 FROM article WHERE name LIKE 'ABC%' 

Answer 4

If you insert one record into your table, you can do

 INSERT INTO yourTable VALUES(value1, value2) 

But since you want to insert more than one record, you can use SELECT FROM in your SQL statement.

so you want to do this:

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

Answer 5

 INSERT INTO prices(group, id, price) SELECT 7, articleId, 1.50 FROM article where name like 'ABC%'; 

Answer 6

The request looks like this:

  insert into table_name (col1,col2,....) values (select col1,col2,... FROM table_2 ...) 

hope this help