SQL Server Insert Query problem

Question

I want to execute this query :

INSERT INTO [Order] (FactorId, ProductId, Entity) 
VALUES((SELECT Top 1 FactorId FROM Factor WHERE UserId = @UserId AND Status = -1), @ProductId, @Entity)

but the following error occurs :

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

Adriaan Stander · Accepted Answer · 2009-09-26 14:42:13Z

4

Try

INSERT INTO [Order] (FactorId, ProductId, Entity) 
SELECT Top 1 FactorId, @ProductId, @Entity FROM Factor WHERE UserId = @UserId AND Status = -1

answered Sep 26, 2009 at 14:42

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

Preet Sangha · Accepted Answer · 2009-09-26 14:37:34Z

0

INSERT INTO [Order] (FactorId, ProductId, Entity) 
SELECT (select Top 1 FactorId FROM Factor WHERE UserId = @UserId AND Status = -1), 
@ProductId, @Entity

answered Sep 26, 2009 at 14:37

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

1 Comment

Mehdi Dehghani Over a year ago

I executed this query, but the following error occurs: The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

anishMarokey · Accepted Answer · 2009-09-26 14:49:58Z

0

try with this

INSERT INTO [Order] (FactorId, ProductId, Entity) 
(SELECT Top 1 FactorId, @ProductId, @Entity FROM Factor 
WHERE UserId = @UserId AND Status = -1)

Note : insert query with select wont allow VALUES keyword :) .

edited Sep 26, 2009 at 14:49

answered Sep 26, 2009 at 14:41

anishMarokey

11.4k2 gold badges36 silver badges47 bronze badges

Comments

j0k · Accepted Answer · 2012-08-29 15:55:42Z

0

if you have other columns involved, avoid the TOP 1 altogether and consider something like this...

INSERT INTO [Order] (FactorId, ProductId, Entity) 
SELECT FactorId, @ProductId, @Entity FROM Factor 
WHERE UserId = @UserId 
  AND Status = -1
  and timestamp = (select max(timestamp) from Factor where UserId = @UserId AND Status = -1)

This concept also works if you have a sort-order column or any other unique column

edited Aug 29, 2012 at 15:55

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Aug 29, 2012 at 15:53

JoeT

1

1 Comment

RichardTheKiwi Over a year ago

There are other better answers. This is more complicated and has the risk of inserting multiples when max(timestamp) is not unique

Collectives™ on Stack Overflow

SQL Server Insert Query problem

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related