error in Multiple Select statements in Insert statement

sql, sql-server, sql-server-2005, sql-server-2008, t-sql

Solution

Just change VALUES to SELECT and remove the outer parentheses.

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 a `TOP 1` on the subexpressions, but that would give a different error message: subquery returned more than one value.

Problem

I am writing a query which has multiple 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) ``` Actually it gives 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 ','. ``` Where these two select statements returns only one value.

Original source