Archives of the TeradataForum
Message Posted: Mon, 09 Sep 2002 @ 18:16:21 GMT
| 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
| 
