Error in multiple select statements in Insert statement

Question

I am writing a query that has several select statements in an insert statement

    INSERT INTO dbo.Products 
    (ProductName, 
     SupplierID, 
     CategoryID, 
     UnitsInStock, 
     UnitsOnOrder, 
     ReorderLevel, 
     Discontinued)
VALUES  
    ('Twinkies' , 
     (SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'),
     (SELECT CategoryID FROM dbo.Categories WHERE CategoryName = 'Confections'), 
     0, 
     0, 
     10, 
     0)

This actually gives an error

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

If these two select statements return only one value.

Answer 1

Just change VALUES to SELECT and remove the outer brackets.

INSERT INTO dbo.Products 
(ProductName, 
 SupplierID, 
 CategoryID, 
 UnitsInStock, 
 UnitsOnOrder, 
 ReorderLevel, 
 Discontinued)
SELECT  
'Twinkies' , 
 (SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'),
 (SELECT CategoryID FROM dbo.Categories WHERE CategoryName = 'Confections'), 
 0, 
 0, 
 10, 
 0

You may also need it TOP 1for subexpressions, but this will give another error message: the subquery returns more than one value.

Answer 2

The error message is correct because

SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'

Can (technically) return multiple rows, which row should be displayed in the column?

, , .

SELECT TOP 1 SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'