How can I combine two SELECT-based TSQL Insertions into one?

Question

0.00/5 (No votes)

See more:

I have these Insert statements in a Stored Procedure:

SQL

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;

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:

SQL

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

...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...?

Posted 15-Jan-16 5:05am

B. Clay Shannon

Updated 15-Jan-16 5:16am

v2

Add a Solution

3 solutions

Solution 1

SQL

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

Posted 15-Jan-16 5:14am

TenmanS14

Updated 15-Jan-16 5:15am

v2

Comments

B. Clay Shannon 15-Jan-16 11:19am

Thanks, but that won't work, because the Week2 values are never populated. It's not a simple "OR" situation, otherwise I could make the conditional part "(INVOICEDATE BETWEEN @BegDate AND @EndDate)"

TenmanS14 15-Jan-16 11:48am

is there a value which will be common between them and unique for each final record, rather than them just being 2 different weeks of data? i.e. could you only have one memberno per week or one of each unit per week?

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price, week2Usage, week2Price)
select InvD1.MEMBERNO, InvD1.ITEMCODE, InvD1.DESCRIPTION, InvD1.QTYSHIPPED, InvD1.PRICE, InvD2.qtyshipped, InvD2.price
FROM INVOICEDETAIL as InvD1
join INVOICEDETAIL as InvD2 on InvD1.unit = InvD2.unit
WHERE InvD1.UNIT=@Unit
AND (INVOICEDATE BETWEEN @BegDate AND @Week1End)

B. Clay Shannon 15-Jan-16 12:01pm

That helps, thanks.

TenmanS14 18-Jan-16 6:41am

LOL, apologies for that, had been in the pub for lunch before I wrote it... on the right lines though I think, I suspect you could achieve what you want with a self join..

Solution 3

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

SQL

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;

Posted 15-Jan-16 6:46am

B. Clay Shannon

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2016-01-15T06:14:00

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

SQL

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

SQL

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;