如何在TSQL存储过程中的临时表中插入多行？ [英] How can I insert multiple rows into a temp table within a TSQL Stored Procedure?

问题描述

我希望我能以这种方式将所有相应的记录插入到临时表中：

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));

...但我得到了，Msg 116，Level 16，State 1，Procedure priceVarianceTest，Line 39

当子查询没有引入EXISTS时，只能在选择列表中指定一个表达式。



这是存储过程更多上下文：

...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));

那我该怎么做呢？我可以使用某种foreach循环，例如（伪代码）：

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)

？

?

推荐答案

你走在正确的轨道上并关闭，只是不要使用VALUES 。所以，做这样的事情：

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;

这篇关于如何在TSQL存储过程中的临时表中插入多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！