Select query on several databases


Select query on several databases



I am using since a couple of days Teradata Studio Express for executing SQL queries.

On my server, I have several databases that contain exactly the same tables.

Example :

- database company1   table customer

- database company2   table customer


The problem is that I have more than 20 databases.

When I want to query for all databases, I have not found another solution to write this kind of script :

select * from company1.customer


select * from company2.customer


select * from company3.customer


It can be very boring at the end !

How can I do easier ? I have not the rights to create procedure, I don't know if it is possible to use a loop statement for a one shot query, I am new in teradata tools.


Thank you for your help.

Junior Contributor

Re: Select query on several databases

What is so hard about writing those UNION ALLs?

Mainly Cut&Paste and a bit Replace.


Re: Select query on several databases

for a simple query, OK.

But when you have queries with several INNER JOIN and consequently several references to database name, it becomes very very difficult and time consuming to have a correct final query !

And it is very easy to forget one replacement and false all your result !

That's why I was dreaming of a more easy and secure procedure using SQL or Teradata Studio Express functionality.