error in Multiple Select statements in Insert statement

Question

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.

harpo · Accepted Answer · 2011-02-02 14:38:23Z

7

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.

answered Feb 2, 2011 at 14:38

harpo

43.5k15 gold badges102 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

overloading Over a year ago

I am following this solution, but it returns

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

. How should I fix this?

harpo Over a year ago

@overloading, the error means just that it says. You have to modify the sub-select to be sure that there is no more than one result. How you do that will depend on your situation. In T-SQL, you can use a TOP 1 with ORDER BY to limit the results, or you can specify further WHERE conditions. It really depends on what you're doing.

overloading Over a year ago

Thanks! Its working now but am not sure why it works. Here's my original statement: INSERT INTO someTable(Date, Id, Constant) SELECT GETDATE(), Id FROM otherTable where Id IN(1,2), 10 this returns the more than 1 value error. But when I do this: SELECT GETDATE(), Id, 10 FROM otherTable where Id IN(1,2); this would work. Could you explain to me why this works? Isnt it still returning 2 rows as result?

harpo Over a year ago

@overloading, your first statement is not valid syntax. Note that the "more than one value" issue only applies to sub-selects, since they have to fill one cell in the output. You can do an INSERT..SELECT with as many rows as you want.

Shrage Smilowitz · Accepted Answer · 2011-02-02 14:26:28Z

0

Error message is correct, since

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

Can (technically) return multiple rows, which row should it display in the column?

If you know that this subquery will only return one row, then tell the database to take the first one.

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

answered Feb 2, 2011 at 14:26

Shrage Smilowitz

25.2k2 gold badges31 silver badges32 bronze badges

Collectives™ on Stack Overflow

error in Multiple Select statements in Insert statement

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related