SQL Server Performance Forum – Threads Archive

subquerries not allowed….

hi, i know from years ago that i had the same problem when i was working with sql server.
when i try to do an "insert into"…."values", and after the values part i put a subquerry, it doesn’t work. i get the error:
Subqueries are not allowed in this context. Only scalar expressions are allowed. but then, how should i write one single statement from within my java program?
i work with plain jdbc connections.
i really need to do an insert, where a value has to be retrieved with another select statement.

---

You don’t really need subquery. You can use insert into . . . select . . . from
insert into sometable ( . . . )
select . . .
from anothertable KH

---

hmmm… how then would you write this: insert into (id, name, company, nationality)
values (‘1’,
select shipname from ships where id = 4,
select shipcompany from companies where id = 4,
‘BE’);

---

how many records are you expecting from
select shipname from ships where id = 4
and
select shipcompany from companies where id = 4 try this
insert into (id, name, company, nationality)
select ‘1’, n.shipname, c.shipcompany, ‘BE’
from shipname n inner join companies c
on n.id = 4
and c.id = 4
OR if you are only expecting one record for the shipname and shipcompany then you can use a variable
select @shipname = shipname from ships where id = 4
select @shipcompany = shipcompany from companies where id = 4 insert into (id, name, company, nationality)
select ‘1’, @shipname, @shipcompany, ‘BE’ KH

---

]]>