Same Query to run in different DB.

Question

Post reply

Same Query to run in different DB.

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
July 1, 2020 at 4:13 pm

#3766768

Hi,
How to execute single query on different databases when one of the column missing in one database table.
Ex1:
select a,b,c from test
we are running above query in different databases in Power BI, All the databases have the same schema,but recently
we need to amend the query to
select a,b,c,d from test. But unfortunately in one of the database (out of 10), column d is not implemented in test table, we don't have permission to add new column d in the test table. Now how can I run this query without throwing error.

Thanks
Sangeeth

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

dkultasev SSC Rookie Points: 34 More actions · Answer 2

If you can't add columns\objects that can only be done by dynamic SQL. If you can create objects, then you can create view that will have placeholder in some databases and actual data in the needed one.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

I agree with the notion of a view that dkultasev posted. If these types of changes are infrequent, then manually building one would be fine. If these tables change a lot, then it might be best to write a little dynamic SQL to interrogate the meta-data of the table in the various databases to build the view on demand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)