CODEWITHSUNDEEP
sqlsql-server
Jakub Stankiewicz
Jakub Stankiewicz

Reputation: 3

USE and UNION ALL

I am working with MS SQL Server and I am trying to gather informations from a few different databases and I need the results in one table.

USE [1st Database]

   select smthing

USE [2nd Database]

   select smthing

USE [3rd Database]

And UNION ALL somewhere between. I cannot find any solution right now.

Upvotes: 0

Views: 370

Answers (2)

Thailo
Thailo

Reputation: 1424

You can run something like this, copy and paste the result in SSMS, then remove the last UNION ALL manually before executing the generated rows:

SELECT
      CONCAT('SELECT TOP 10 make_id, make_name FROM ', db.name, '.dbo.[Makes] UNION ALL')
FROM  sys.databases AS db
WHERE db.name LIKE 'MyCarsDBs%';

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can reference the database name along with table : 

select t.col1, t.col2, . . . 
from <db-name>.<schema>.<tablename> t union all
select t.col1, t.col2, . . . 
from <db-name>.<schema>.<tablename> t;

This needs to access to the all databases you included in the select statement.

Upvotes: 4

Related Questions