SQL - Pass a variable in an INSERT subquery

Hello Experts!

I am trying to insert some data into a SQL Server table from a C# application. Normally I would have no trouble doing this, but I am running into an error when I try to pass a variable in the insert subquery.

I have a table [CapitalRequest] where a [RequestNumber] is assigned an [ID]. I want to select that [ID] where [RequestNumber] = @capNum and INSERT it into [ExpenseItem].

Here is the error message:

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

Thanks in advance!

Here is my SQL code:

INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
     VALUES
           (@iCat,@iDesc,@iCost,@iQnty,(SELECT [id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))

Open in new window

I_sAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems like there are multiple IDs with [capitalNumber] = @capNum ; so you have 2 options ,
 if you want to insert the last value of ID
,(SELECT MAX([id]) FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))
or if you want to insert all the records
INSERT INTO [ExpenseItem]           ([itemCategory]           ,[itemDescription]           ,[itemCost]           ,[itemQuantity]           ,[capitalId])  
select @iCat,@iDesc,@iCost,@iQnty,[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum
0
 
TonyRebaCommented:
Try declaring a variable and then using that as a condition in your query..
0
 
Umar Topia.Net Full Stack DeveloperCommented:
INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
     VALUES
           (@iCat,@iDesc,@iCost,@iQnty,[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
TonyRebaCommented:
INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
SELECT

(@iCat,@iDesc,@iCost,@iQnty,@id FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))
0
 
nishant joshiTechnology Development ConsultantCommented:
 //best way to use sql command and prevention from sql injection
            SqlCommand cmd=new SqlCommand("INSERT INTO ExpenseItem(itemCategory,itemDescription,itemCost,itemQuantity,capitalId)VALUES(@iCat,@iDesc,@iCost,@iQnty,(SELECT [id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))");
            cmd.Parameters.Add("@iCat", SqlDbType.Int);//add parameters
            cmd.Parameters["@iCat"].Value = " ";//set value here

Open in new window

0
 
I_sAuthor Commented:
Thanks guys,

@aneeshattingal

Exactly what I needed!

INSERT INTO [ExpenseItem]          
                   ([itemCategory]
                  ,[itemDescription]  
                  ,[itemCost]    
                  ,[itemQuantity]  
                  ,[capitalId])  
SELECT        @iCat,
                  @iDesc,
                  @iCost,
                  @iQnty,
                  [id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum

CapitalNumber and ID are unique in the CapitalRequest table, so I should be able to use this query.

Thanks for your quick reply!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.