如何将两个基于SELECT的TSQL插入组合成一个？ [英] How can I combine two SELECT-based TSQL Insertions into one?

问题描述

我在存储过程中有这些Insert语句：

I have these Insert statements in a Stored Procedure:

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End;

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week2Usage, Week2Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;

他们获得了正确的数据，但每行（当然）有Week2Usage和Week2Price的空白值（用第一个块插入的行）或Week1Usage和Week1Price的空白值（用第二个块插入的行）。



如何组合这两个语句以便一个Insert将填充所有这四个字段，没有空的WeekN值？



我想也许是这样的工作：

They get the right data, but each row has (of course) blank values for either Week2Usage and Week2Price (the rows inserted with the first block) or blank values for Week1Usage and Week1Price (the rows inserted with the second block).

How can I combine these two statements so that one Insert will populate all four of those fields, with no empty "WeekN" values?

I thought maybe something like this work:

INSERT INTO #temp1 (MemberNo, MemberItemCode, PADescription, Week1Usage, Week2Usage, Week1Price, Week2Price) 
    select MEMBERNO, ITEMCODE, DESCRIPTION, 
    (select QTYSHIPPED FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @BegDate AND @Week1End), 
    (select QTYSHIPPED FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate), 
    (select PRICE FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @BegDate AND @Week1End), 
    (select PRICE FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate), 
    FROM INVOICEDETAIL 
    WHERE UNIT=@Unit

...但它给了我：



Msg 512，Level 16，State 1，Procedure priceVarianceTest，Line 39

子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。

语句已终止。



是否有解决方法，或者我是否需要创建另一个临时表，然后填充组合这些值的新表，或者...... ？

...but it gives me:

Msg 512, Level 16, State 1, Procedure priceVarianceTest, Line 39
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Is there a workaround for this, or do I need to create another temp table and then populate a new table combining those values, or...?

推荐答案

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND ((INVOICEDATE BETWEEN @BegDate AND @Week1End) OR (INVOICEDATE BETWEEN @Week2Begin AND @EndDate))

作为替代解决方案1，您也可以使用简单的UNION操作。换句话说

As an alternative to solution 1, you can also use simply UNION operation. In other words

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @BegDate AND @Week1End
UNION ALL
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @Week2Begin AND @EndDate;

关于列的更正

Correction concerning the columns

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price, Week2Usage, Week2Price)
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE, NULL, NULL
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @BegDate AND @Week1End
UNION ALL
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, NULL, NULL, QTYSHIPPED, PRICE
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @Week2Begin AND @EndDate;

感谢TenmanS14的想法，以下工作（尽管数据尚未确定，至少它将第一周和第二周结合起来）。

Thanks to TenmanS14 for his idea, the following works (although the data is not right yet, at least it is combining the week1 and week2 vals).

create table #temp1
(
	MemberNo varchar(25),
	MemberItemCode varchar(25),
	ShortName varchar(25),
	ItemCode varchar(50),
        WVItem varchar(25),
	WVItemCode varchar(25),
	WVDescription varchar(250),
	Week1Usage varchar(25),
	Week2Usage varchar(25),
	UsageVariance varchar(25),
	Week1Price varchar(25),
	Week2Price varchar(25),
	PriceVariance varchar(25),
	PercentageOfPriceVariance varchar(25)
)

-- populate week1 vals
INSERT INTO #temp1 (MemberNo, MemberItemCode, WVDescription, Week1Usage, Week1Price) 
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL 
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End;

create table #temp2
(
	MemberNo varchar(25),
	Week2Usage varchar(25),
	Week2Price varchar(25)
)

-- populate week2 vals
INSERT INTO #temp2 (MemberNo, Week2Usage, Week2Price)
select MEMBERNO, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;

-- now I have some records with week1 vals, and some with week2 vals; combine them into a third table

create table #tempCombined
(
	MemberNo varchar(25),
	MemberItemCode varchar(25),
	ShortName varchar(25),
	ItemCode varchar(50),
        WVItem varchar(25),
	WVItemCode varchar(25),
	WVDescription varchar(250),
	Week1Usage varchar(25),
	Week2Usage varchar(25),
	UsageVariance varchar(25),
	Week1Price varchar(25),
	Week2Price varchar(25),
	PriceVariance varchar(25),
	PercentageOfPriceVariance varchar(25)
)

INSERT INTO #tempCombined (MemberNo, MemberItemCode, WVDescription, Week1Usage, Week1Price, week2Usage, week2Price)
select t1.MEMBERNO, t1.ITEMCODE, t1.WVDESCRIPTION, t1.Week1Usage, t1.Week1Price, t2.Week2Usage, t2.Week2Price
FROM #temp1 as t1
join #temp2 as t2 on t1.MemberNo = t2.MemberNo

select * from #tempCombined ORDER BY WVDescription, Week1Usage DESC;

这篇关于如何将两个基于SELECT的TSQL插入组合成一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！