Inner join in Insert query?

Question

I wanna do something like this:

insert into TableA 
   (val1,val2) 
values
   ("value",(select top 1 tableB.X from tableB where tableB.Y=@Y))

I get this error:

Subqueries are not allowed in this context. Only scalar expressions are allowed

How to stop that error?

Answer 1

Assuming you're using SQL Server:

insert into tableA 
  (val1, val2) 
select top 1 'value', tableB.x from tableB where tableB.Y = @y 

Answer 2

I assume you have to use directly the insert into TABLE select... syntax.
No "values" in this case.
People above have been faster than me but I agree with their proposals