Multi-Table Insert Statements

Database

Multi-Table Insert Statements

Hi,

Oracle DB offers a so-called multi-table insert statement which is very handy for ELT processing. It offers the possibility to insert multiple rows into one or more target tables from one source row. Here is a short example that should demonstrate what it does: 

-- source: source_table_1 - table with many columns

-- target 1: agmt_metric - contains only identifier and key/value pairs

-- target 2: agmt_feature - contains only identifier and key/value pairs

INSERT ALL

    WHEN st1.deal_type = 1 THEN

        INTO agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP1', st1.some_value)

    WHEN st1.deal_type = 1 THEN

        INTO agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP2', st1.some_other_value)

    WHEN st1.deal_type = 2 THEN

        INTO agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP1', st1.yet_another_other_value)

    WHEN st1.deal_type = 1 THEN

        INTO agmt_feature (agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF1', st1.some_property)

    WHEN st1.deal_type = 1 THEN

        INTO agmt_metric (agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF2', st1.some_other_property)

    WHEN st1.deal_type = 2 THEN

        INTO agmt_metric (agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF1', st1.yet_another_other_propoerty)

SELECT * from source_query_1 st1;   

What is the best way to achieve something like the above on Teradata? Unfortunately, our ELT tool does not offer great support for multi-statement requests.

Thanks for your ideas!

Hans

Tags (2)
6 REPLIES
Enthusiast

Re: Multi-Table Insert Statements

Hi,

You will need to create a stored procedure to acheive this in Teradata.

create procedure my_proc()
begin
for st1 as cur cursor for
(
select
account_no
,some_value
,some_other_value
,yet_another_other_value
,some_property
,some_other_property
,yet_another_other_propery
,deal_type
from source_query_1
)
do
if st1.deal_type = 1 then
insert into agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP1', st1.some_value);
end if;
if st1.deal_type = 1 then
insert into agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP2', st1.some_other_value);
end if;
if st1.deal_type = 2 then
insert into agmt_metric (agmt_id, metric_type_cd, metric_value) VALUES (st1.account_no, 'TP1', st1.yet_another_other_value);
end if;
if st1.deal_type = 1 then
insert into agmt_feature(agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF1', st1.some_property);
end if;
if st1.deal_type = 1 then
insert into agmt_metric (agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF2', st1.some_other_property);
end if;
if st1.deal_type = 2 then
insert into agmt_metric (agmt_id, feature_type_cd, feature_value) VALUES (st1.account_no, 'TF1', st1.yet_another_other_propery);
end if;
end for;
end;

And run the procedure using below syntax

call my_proc();

Regards

Senior Apprentice

Re: Multi-Table Insert Statements

Teradata SQL doesn't support multi-table inserts.

Only Teradata's load utilities (MLOAD/TPUMP/TPT LOAD/TPT STREAM) can do what you want using multiple APPLYs.

You might split the inserts in the load and then simply insert/select in the target tables...

Re: Multi-Table Insert Statements

Thanks for the replies! Do you know if there are any plans for such a feature in a future release?

Regards

Hans

Enthusiast

Re: Multi-Table Insert Statements

This is just my opinion.

I don't think Oracle insert all is great. The question of restartability is there. If it rolls back for a huge amount of data in the middle????. Think about a failure in the middle and the amount of data is huge. It may be a wastage of time. Again it is just my opinion.

Enthusiast

Re: Multi-Table Insert Statements

@dnoeth Can we do through calling procedure which is explanied by one of the user?

Senior Apprentice

Re: Multi-Table Insert Statements

If the number of rows is very small you can do the cursor approach, otherwise it's wwwwwaaaaaayyyyyy to slow.