Click here to Skip to main content
15,888,351 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I was hoping I could insert all the corresponding records into a temp table this way:

SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode) VALUES ((select MEMBERNO, ITEMCODE FROM INVOICEDETAIL WHERE UNIT=@Unit));


...but I get, "Msg 116, Level 16, State 1, Procedure priceVarianceTest, Line 39
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Here is the Stored Procedure with a little more context:
SQL
CREATE Procedure [dbo].[priceVarianceTest]
	@Unit varchar(25),
AS 

create table #temp1
(
	MemberNo varchar(25),
	MemberItemCode varchar(25),
	. . .
)

INSERT INTO #temp1 (MemberNo, MemberItemCode) VALUES ((select MEMBERNO, ITEMCODE 
FROM INVOICEDETAIL WHERE UNIT=@Unit));


So how can I accomplish this? Is there some sort of "foreach" loop that I can use, such as (pseudocode):

SQL
FOREACH RECORD IN INVOICEDETAIL WHERE UNIT=@UNIT
	INSERT INTO #temp1 (MemberNo, MemberItemCode) VALUES (MEMBERNO, ITEMCODE)


?
Posted

1 solution

You're on the right track and close, just don't use the VALUES. So, do something like this:

SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode) 
select MEMBERNO, ITEMCODE 
FROM INVOICEDETAIL 
WHERE UNIT=@Unit;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900