How can I insert values into a table, using a subquery with more than one result?

SqlInsertSubquery

Sql Problem Overview

I really would appreciate your help.

Probably it's a quite simple problem to solve - but I'm not the one .. ;-)

I have two tables in SQL Server:

  1. article
  2. prices

Now I want to select a certain set of ids and insert some entries into the prices-table with those ID.

e.g. (wrong and not working SQL)

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 help

Sql Solutions

Solution 1 - Sql

You want:

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

where you just hardcode the constant fields.

Solution 2 - Sql

Try this:

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

Solution 3 - Sql

If you are inserting 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 a SELECT FROM in your SQL statement.

so you will want to do this:

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

Solution 4 - Sql

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

Solution 5 - Sql

the sub query looks like

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

hope this help

Solution 6 - Sql

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

Solution 7 - Sql

i am using this method.

WITH selected AS (
   SELECT articleId FROM article WHERE name LIKE 'ABC%'
) INSERT INTO prices(group, id, price)
  SELECT 7, articleId, 1.50 from selected;
Categories
Recommended Sql Solutions
Recommended Insert Solutions
Recommended Subquery Solutions

Previous Solution:

Convert HH:MM:SS string to seconds only in javascript

JavascriptTime

Next Solution:

Get an element by index in jQuery

JqueryDomGet

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionFuturetecView Question on Stackoverflow
Solution 1 - SqlMike RyanView Answer on Stackoverflow
Solution 2 - SqlStefan HView Answer on Stackoverflow
Solution 3 - SqlTarynView Answer on Stackoverflow
Solution 4 - SqlTerkelView Answer on Stackoverflow
Solution 5 - SqlMuhannad A.AlhaririView Answer on Stackoverflow
Solution 6 - SqlTejaView Answer on Stackoverflow
Solution 7 - Sqluser12829430View Answer on Stackoverflow