I was hoping I could insert all the corresponding records into a temp table this way:
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:
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):
FOREACH RECORD IN INVOICEDETAIL WHERE UNIT=@UNIT
INSERT INTO #temp1 (MemberNo, MemberItemCode) VALUES (MEMBERNO, ITEMCODE)
?