create table temp_pe_rpt_prm_subscrs (
f_division integer,
link integer,
created_at timestamp default current_timestamp
);
insert into temp_pe_rpt_prm_subscrs (f_division, link) values
(36, 11),
(36, 12);
create table temp_pe_rpt_763_025_12_devices_tmp_rpt_ss (
f_sale_categories integer,
f_sale_items integer,
n_period integer,
f_division integer,
f_subscr integer,
created_at timestamp default current_timestamp
);
insert into temp_pe_rpt_763_025_12_devices_tmp_rpt_ss (f_sale_categories, f_sale_items,
n_period, f_division, f_subscr) values
(16, 23, 2023, 36, 11),
(16, 24, 2023, 36, 12);
MERGE INTO temp_pe_rpt_763_025_12_devices_tmp_rpt_ss AS tb
USING (
SELECT
ss.f_division,
ss.link AS F_Subscr,
fsc.f_sale_categories,
fsc.f_sale_items,
tp.n_period
FROM temp_pe_rpt_prm_subscrs AS ss
CROSS JOIN (
SELECT DISTINCT
T.f_sale_categories,
T.f_sale_items
FROM temp_pe_rpt_763_025_12_devices_tmp_rpt_ss t
) fsc
CROSS JOIN (
SELECT DISTINCT
t.n_period
FROM temp_pe_rpt_763_025_12_devices_tmp_rpt_ss t
) tp
) AS ts
ON tb.f_division = ts.f_division
AND tb.f_subscr = ts.F_Subscr
AND tb.f_sale_categories = ts.f_sale_categories
AND tb.f_sale_items = ts.f_sale_items
AND tb.n_period = ts.n_period
WHEN NOT MATCHED THEN
INSERT
(
f_division,
F_Subscr,
f_sale_categories,
f_sale_items,
n_period
)
VALUES
(
ts.f_division,
ts.F_Subscr,
ts.f_sale_categories,
ts.f_sale_items,
ts.n_period
);
select * from temp_pe_rpt_763_025_12_devices_tmp_rpt_ss
Telegram: DB developers channel