Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 09 Sep 2002 @ 18:16:21 GMT


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


Subj:   Re: Macro to loop thru different databases
 
From:   Geoffrey Rommel

  select count(*) from database1.customers  


  select (*) from database2.customers  


  what I want to do is to automatically do a 'union all' on the above database1-2...I actually have database1-10 and did not want to run the same query for 10 markets.  



Bruce, your use of the word 'loop' makes me nervous. I think you are asking whether you can code a macro that will produce a union of selections from an arbitrary number of input tables, along these lines:

for i = 1 to any_number;
  insert into target_table
   select stuff from database[i].customers;
end;

This is impossible. As often mentioned in this forum, the names of databases and tables must be hard-coded in macros so that the parser can bind them properly.

If, however, you know the names of all your databases, you can indeed code a macro to insert all your selections using one of these techniques:

(1) Union:
create macro strangular.mac01 as
(
insert into target_table
select stuff from database1.customers
UNION
select stuff from database2.customers
UNION ... etc. ;);

(2) Insert/select (note: this is *NOT* a multi-statement request):
create macro strangular.mac01 as
(
insert into target_table
select stuff from database1.customers;
insert into target_table
select stuff from database2.customers;
etc. ;);

--wgr



     
  <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: 15 Jun 2023