Insert with select subquery using SqlCommand

Question

I am having a problem when using an INSERT statement with a nested select. The query works when executing it in the SQLManagement Studio but returns an error when executing it in code.

The query looks like :

INSERT INTO [Projects] 
     VALUES ('1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',
  (SELECT status.statusID from status where name = 'STOPPED')

)

The error returned :

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

Is there an explanation for this and how will go to solve this issue as I do not know what the id of the Status is, besides executing a separate select query?

gbn · Accepted Answer · 2010-09-08 08:51:27Z

3

You could try this...

INSERT INTO [Projects] 
SELECT '1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',
status.statusID from status where name = 'STOPPED'

The error makes sense because you could have multiple rows from the sub query. In my suggestion, you could also end up with multiple rows too. Should there be more to the filter?

answered Sep 8, 2010 at 8:51

gbn

435k84 gold badges602 silver badges690 bronze badges

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

1 Comment

fluf Over a year ago

Thanks now I see why the error is thrown, there isn't really anything else to filter on. I was kinda going on the assumption that the select would only return 1 value

AEMLoviji · Accepted Answer · 2010-09-08 08:53:50Z

1

The best way is to create Stored Procedure.

declare @statusID int;

SELECT @statusID=status.statusID from status where name = 'STOPPED'

INSERT INTO [Projects] 
     VALUES ('1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',@statusID);

answered Sep 8, 2010 at 8:53

AEMLoviji

3,2779 gold badges41 silver badges61 bronze badges

Collectives™ on Stack Overflow

Insert with select subquery using SqlCommand

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related