Using insert into ... select results in a incorrect syntax near select, why?

Question

How can I make a SELECT inside an INSERT operation?

insert into tableX (a_id, b_id)  VALUES ((SELECT service_id           FROM tableY           WHERE id = 10, 2)); 

But the server returns a syntax error, why?

SQL Error [156] [S0001]: Incorrect syntax near the keyword 'select'. 

Answer 1

While my original answer gave a working solution, I was actually wrong about the cause of the error. There is nothing wrong with using a scalar subquery inside a VALUES clause. The problem with the statement in the question is simply that one parenthesis is in the wrong place; the scalar subquery must be enclosed in parentheses.

This should work:

insert into tableX (a_id, b_id)  VALUES (   (SELECT service_id           FROM tableY           WHERE id = 10)   , 2   ); 

Original Answer

VALUES can only be used in conjunction with literal values. However, literal values can be used in a subquery. Do this:

insert into tableX (a_id, b_id) SELECT service_id, 2 FROM tableY WHERE id = 10