Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Mon, 09 Sep 2002 @ 20:14:44 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Macro to loop thru different databases
 
From:   deWet, Johannes M

Bruce,

Try the following. It might solve your problem using a stored procedure and not a macro as you asked, but the end result is the same. You also might want to change it a little to better suit your needs.

The stored procedure uses DBC.COLUMNS to determine which databases contains your source tables. It will insert the contents from each of those databases into your destination database and table, until it runs out of databases that contain your source system tablename.

REPLACE PROCEDURE mydb.MY_INSERT(destination_DB VARCHAR(30),destination_table VARCHAR(30), source_table VARCHAR(30))
BEGIN

DECLARE dbname VARCHAR(128);
L1:
FOR databasecursor AS dbcursor CURSOR FOR
SELECT  databasename
FROM    dbc.tables
WHERE   tablename =  :source_table
GROUP   BY 1
        DO
        SET dbname = databasecursor.databasename;
        CALL DBC.SysExecSQL('INSERT INTO ' || :destination_DB || '.' || :destination_table || ' SEL * FROM  ' || :dbname ||'.' ||
:source_table);
END     FOR L1;

END;

CALL  mydb.my_insert ('mydb','my_destination_table','my_source_table');

Hope it helps,

Johannes



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020