INSET SELECT with Union

[Dan]

Can you construct an INSERT SELECT query where the SELECT
query contains a UNION clause?

If yes, how so?

I am getting a syntax error -- see the query below.

Thanks,

insert into card_inventory
(EFF_DTE,PROGRAM_ID,Current_Inventory)

SELECT datevalue(j.req_ent_dte) as [eff_Dte],
e.program_id,l.current_inventory-count(*) as
current_inventory
FROM bank_txn AS a, code_value AS b, program_req_distribtn
AS c, program_req AS d, program AS e, merchant AS f,
mloc_bank AS g, merchant_location AS h, merchant_location
AS i, acct_req_res AS j, code_value as k, card_inventory l
WHERE a.txn_type_cde=b.code_id And b.code_type='REQ_TYPE'
And
a.program_req_distribtn_id=c.program_req_distribtn_id
And c.program_req_id=d.program_req_id And
d.program_id=e.program_id
And c.mloc_bank_id=g.mloc_bank_id
And g.merchant_location_id=h.merchant_location_id
And h.merchant_id=f.merchant_id
And a.acct_req_res_id=j.acct_req_res_id
And j.merchant_location_id=i.merchant_location_id
And datevalue(j.req_ent_dte) = date()-18
and e.program_id = l.program_id
and l.eff_dte = date()-19
and c.fee_type_cde = k.code_id and k.code_type
= 'FEE_TYPE' and k.code_id in( '15','16','18','21')
GROUP BY datevalue
(j.req_ent_dte),e.program_id,l.current_inventory

union

select date()-18 as eff_dte, program_id, current_inventory
from card_inventory
where eff_dte = date()-19
and not exists

(select datevalue(j.req_ent_dte) as [eff_Dte], e.program_id
FROM bank_txn AS a, code_value AS b, program_req_distribtn
AS c, program_req AS d, program AS e, merchant AS f,
mloc_bank AS g, merchant_location AS h, merchant_location
AS i, acct_req_res AS j, code_value as k
WHERE a.txn_type_cde=b.code_id And b.code_type='REQ_TYPE'
And a.program_req_distribtn_id=c.program_req_distribtn_id
And c.program_req_id=d.program_req_id And
d.program_id=e.program_id And
c.mloc_bank_id=g.mloc_bank_id And
g.merchant_location_id=h.merchant_location_id And
h.merchant_id=f.merchant_id And
a.acct_req_res_id=j.acct_req_res_id And
j.merchant_location_id=i.merchant_location_id And datevalue
(j.req_ent_dte) = date()-18
and c.fee_type_cde = k.code_id and k.code_type
= 'FEE_TYPE' and k.code_id in( '15','16','18','21')
GROUP BY datevalue(j.req_ent_dte),e.program_id)

 

[John Vinson]

Can you construct an INSERT SELECT query where the SELECT
query contains a UNION clause?
Yes.

If yes, how so?

As two queries, one based upon the other. Create the UNION query and
save it (as uniAllInserts say).

Then

INSERT INTO card_inventory (EFF_DTE, PROGRAM_ID, Current_Inventory)
SELECT Eff_Dte, Program_ID, Current_Inventory FROM uniAllInserts.