Home How to loop a query in different databases in T-SQL?
Reply: 0

How to loop a query in different databases in T-SQL?

user1732
1#
user1732 Published in June 19, 2018, 6:14 pm

I have the following query that needs to be executed in different databases but table name and its structure is the same.

--query to be executed
select cust_num, name, credit_hold, credit_hold_reason, 
   (case 
       when credit_hold_reason = 'NP' then 'No payments'
       when credit_hold_reason = 'UK' then 'Unknown'
       when credit_hold_reason = 'BK' then 'Bankruptcy'
    end) as 'Type of credit hold reason',
 credit_hold_date, credit_limit
from [database_01].[dbo].[custaddr] 
--- from [database_02].[dbo].[custaddr] 
--- from [database_03].[dbo].[custaddr] 
order by credit_hold_reason asc

As you see, I just need to change the name of the database or "loop" in some sort of variable that holds the name of the databases, i.e.,

tables_in_loop = ['[database_01].[dbo].[custaddr]', 'from [database_02].[dbo].[custaddr]', 'from [database_03].[dbo].[custaddr]']

When you have the name of the databases, a connection is generated to them and all these databases are in the same server.

I do not have any approach or scratch code about this because this involves more depth in knowledge in SQL and unfortunately I am not an expert - yet. I read the following post SQL Server: how to get a database name as a parameter in a stored procedure and the approach to solve it seems similar here:

declare @dbname nvarchar(255);    
set @dbname = 'db1';    

if @dbname = 'db1'
    use db1;
else if @dbname = 'db2'
    use db2;

but @dbname needs to increased to the next database and this is not specified in the link.

Any help or code in here would be appreciated.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310027 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO