All Forums Database
hallerj 2 posts Joined 05/11
05 Feb 2015
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:
hemanth.gudela 10 posts Joined 01/15
05 Feb 2015

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

-Hemanth Gudela

dnoeth 4628 posts Joined 11/04
05 Feb 2015

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...
 
 

Dieter

hallerj 2 posts Joined 05/11
06 Feb 2015

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

Raja_KT 1246 posts Joined 07/09
06 Feb 2015

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.