Select query on several databases

General
Fan

Select query on several databases

Hello,

 

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

UNION ALL

select * from company2.customer

UNION ALL

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.

2 REPLIES
Junior Contributor

Re: Select query on several databases

What is so hard about writing those UNION ALLs?

Mainly Cut&Paste and a bit Replace.

Fan

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.