Click here to Skip to main content
14,661,175 members
Rate this:
Please Sign up or sign in to vote.
See more:
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)


?
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100