Multiple query statement insert into temptable in procedure

Question

0.00/5 (No votes)

See more:

how can I instert into temptable with returned values from multiple query statement in a same row?

I tried below code, but get result of
----------------------
OPC PFC BCFC
10 NULL NULL
11 NULL NULL
12 NULL NULL
NULL NULL 0
NULL NULL 0
NULL NULL 0
----------------------

What I have tried:

ALTER procedure [dbo].[proc_test]
(@currentdate datetime)
as 
begin
create table #tempRearRight
(OPC INT, PFC INT, BCFC INT)

INSERT INTO #tempRearRight (opc)
select Count ( * ) as OPC  from D_Aktion
where Zeitstempel between (dateadd(day, -1,(@currentdate)) + '' + '23:30:00.000') and ( (@currentdate) + '' + '00:30:00.000')
and Formular = 'ASSEMBLY_ORDER_2PARTS' and Druckstatus = 1
and Drucker = 'BO_RR'

INSERT INTO #tempRearRight (opc)
select Count ( * ) as OPC from D_Aktion
where Zeitstempel between  ((@currentdate) + '' + '00:30:00.000') and ((@currentdate) + '' + '01:30:00.000')
and Formular = 'ASSEMBLY_ORDER_2PARTS' and Druckstatus = 1
and Drucker = 'BO_RR'

INSERT INTO #tempRearRight (opc)
select Count ( * ) as OPC from D_Aktion
where Zeitstempel between  ((@currentdate) + '' + '01:30:00.000') and ((@currentdate) + '' + '02:30:00.000')
and Formular = 'ASSEMBLY_ORDER_2PARTS' and Druckstatus = 1
and Drucker = 'BO_RR'

INSERT INTO #tempRearRight (BCFC)
Select count ( * ) as BCFC from qspruefauftrag_kopf
where Statuszeitstempel between (dateadd(day, -1,(@currentdate)) + '' + '23:30:00.000') and ( (@currentdate) + '' + '00:30:00.000')
and (status = 2  
	OR status = 3) --Status = 3 means 'OK', Status = 2 means 'NOK'
and QSPruefprofil = 'TSV HR'

INSERT INTO #tempRearRight (BCFC)
Select count ( * ) as BCFC from qspruefauftrag_kopf
where Statuszeitstempel between ((@currentdate) + '' + '00:30:00.000') and ( (@currentdate) + '' + '01:30:00.000')
and (status = 2  
	OR status = 3) --Status = 3 means 'OK', Status = 2 means 'NOK'
and QSPruefprofil = 'TSV HR'

INSERT INTO #tempRearRight (BCFC)
Select count ( * ) as BCFC from qspruefauftrag_kopf
where Statuszeitstempel between ((@currentdate) + '' + '01:30:00.000') and ( (@currentdate) + '' + '02:30:00.000')
and (status = 2  
	OR status = 3) --Status = 3 means 'OK', Status = 2 means 'NOK'
and QSPruefprofil = 'TSV HR'
	select * from #tempRearRight
end

exec proc_test '2022-03-16'

Posted 3-Aug-22 17:18pm

zz說劇 2022

Updated 3-Aug-22 19:55pm

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2022-08-03T19:55:00

Each time you execute an INSERT statement, it creates a new row: if you supply just one column value with the INSERT then all other columns get defaulted - so because you issue six INSERT commands, you get six rows.
The simplest way round this is to pregenerate all the values into SQL variables in your SP, then issue three INSERT commands:

SQL

CREATE TABLE #tempRearRight
(OPC INT, PFC INT, BCFC INT)

DECLARE @OPC int = (SELECT COUNT(*) FROM ...);
DECLARE @BCFC int = (SELECT COUNT(*) FROM ...);

INSERT INTO #tempRearRight (opc, bcfc)
VALUES (@OPC, @BCFC);
... change @OPC and @BCFC ...
INSERT INTO #tempRearRight (opc, bcfc)
VALUES (@OPC, @BCFC);
... change @OPC and @BCFC ...
INSERT INTO #tempRearRight (opc, bcfc)
VALUES (@OPC, @BCFC);

SELECT * FROM #tempRearRight
DROP TABLE #tempRearRight