136

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Futuretec
  • 1,371
  • 2
  • 9
  • 4
  • 13
    Wow! So many correct answers all on Mar 13 '12 at 21:18 (hint: you can hover over the timestamp to get the seconds) – tim-phillips Dec 07 '17 at 21:53

7 Answers7

213

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.

Mike Ryan
  • 4,234
  • 1
  • 19
  • 22
  • 1
    INSERT INTO `iden_course` (Cse_M_ID,`Cse_M_Name`,`Cse_M_ShName`, `Cse_M_TotSem`,Cse_M_CreatedDate) VALUES ('ID','BJf', 'BJfg' , '4',Now()) select max(Cse_M_ID) as ID from iden_course how to add in this query – SANDEEP Sep 20 '14 at 10:57
31

Try this:

INSERT INTO prices (
    group, 
    id,
    price
) 
SELECT
    7,
    articleId,
    1.50
FROM
    article 
WHERE 
    name LIKE 'ABC%';
Stefan H
  • 6,635
  • 4
  • 24
  • 35
16

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%'
Taryn
  • 242,637
  • 56
  • 362
  • 405
14
INSERT INTO prices (group, id, price)
  SELECT 7, articleId, 1.50 FROM article WHERE name LIKE 'ABC%'
Terkel
  • 1,575
  • 8
  • 9
5

the sub query looks like

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

hope this help

Muhannad A.Alhariri
  • 3,702
  • 4
  • 30
  • 46
3

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;
  • From a performance perspective, this method is much preferred and yielded me a gain of x27 on PostgreSQL 12, relative to the accepted answer. – chjortlund Oct 28 '22 at 12:58
1
INSERT INTO prices(group, id, price)
SELECT 7, articleId, 1.50
FROM article where name like 'ABC%';
Teja
  • 13,214
  • 36
  • 93
  • 155