I have two tables in SQL Server:
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
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.
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 queryTry this:
INSERT INTO prices (
group,
id,
price
)
SELECT
7,
articleId,
1.50
FROM
article
WHERE
name LIKE 'ABC%';
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%'
INSERT INTO prices (group, id, price)
SELECT 7, articleId, 1.50 FROM article WHERE name LIKE 'ABC%'
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;
the sub query looks like
insert into table_name (col1,col2,....) values (select col1,col2,... FROM table_2 ...)
hope this help
INSERT INTO prices(group, id, price)
SELECT 7, articleId, 1.50
FROM article where name like 'ABC%';
