SQL (combine multiple insert-sel statements into one )

General

SQL (combine multiple insert-sel statements into one )

Hi ,

I want to merge following multiple insert - select statements into a single insert-select statement without using UNION/UNION ALL. Please suggest.

INSERT INTO Fact

SELECT DATE AS LOAD_DT

, key_id

, 200 AS S_id

, device_id

, seg_id

, insight_id

, 5040 AS m_id

, SUM(no_pmt) AS VAL

FROM Payment

GROUP BY 1,2,3,4,5,6,7;

INSERT INTO Fact

SELECT DATE AS LOAD_DT

, key_id

, 200 AS S_id

, device_id

, seg_id

, insight_id

, 5050 AS m_id

, SUM(AMOUNT) AS VAL

FROM Payment

GROUP BY 1,2,3,4,5,6,7;

INSERT INTO Fact

SELECT DATE AS LOAD_DT

, key_id

, 200 AS S_id

, device_id

, seg_id

, insight_id

, 5060 AS m_id

, COUNT(DISTINCT c_id) AS VAL

FROM Payment

GROUP BY 1,2,3,4,5,6,7;

Tags (1)
4 REPLIES
N/A

Re: SQL (combine multiple insert-sel statements into one )

INSERT INTO FACT
SEL
 LOAD_DT
,key_id
,S_id
,device_id
,seg_id
,insight_id
,CASE WHEN T2.ROW_NUM =1 THEN 5040
   WHEN T2.ROW_NUM=2 THEN 5050
   ELSE 5060  END AS M_ID
,CASE WHEN T2.ROW_NUM =1 THEN T1.VAL1
   WHEN T2.ROW_NUM=2 THEN T1.VAL2
   ELSE T1.VAL3  END AS VAL
(
SELECT DATE AS LOAD_DT
 , key_id
 , 200 AS S_id
 , device_id
 , seg_id
 , insight_id
 , SUM(no_pmt) AS VAL1
 , SUM(AMOUNT) AS VAL2
 , COUNT(DISTINCT c_id) AS VAL3
 FROM Payment
 GROUP BY 1,2,3,4,5,6
 ) T1
  CROSS JOIN
( SEL
ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY 'A' ) AS ROW_NUM
FROM SYS_CALENDAR.Calendar
Qualify ROW_NUM <=3
) T2

Please check if this suites your need.

Teradata Employee

Re: SQL (combine multiple insert-sel statements into one )

Any specific reason for not using UNION/UNION ALL?

Re: SQL (combine multiple insert-sel statements into one )

Hi Adeel,

I just want to compare the performace of both queries and then decide, which one to implement finally.

Re: SQL (combine multiple insert-sel statements into one )

Hi Kirti,

Thanks for help. It seems I have to go with UNION ALL as the suggested query is not returning the appropriate data. I tried another way as below but that too didn't produce desired result :( .

create volatile table MID

( MID integer ) on commit preserve rows ;

insert into MID values ( 5040) ;

insert into MID values ( 5050) ;

insert into MID values ( 5060) ;

collect stats on MID column (MID) ;

INSERT INTO Fact

SELECT DATE AS LOAD_DT

, key_id

, 200 AS S_id

, device_id

, seg_id

, insight_id

, mid.mid as  m_id

, case when  mid.mid  = 5040 then SUM(no_pmt)

            when  mid.mid  = 5050 then SUM(AMOUNT)

         when  mid.mid  = 5060 then  COUNT(DISTINCT c_id) END AS VAL

FROM Payment  cross join MID

GROUP BY 1,2,3,4,5,6,7;