Archives of the TeradataForum

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

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

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