Loop with DB names inserting

Question

I have a number of databases with tables Table1 with the same structures. All table names are listed in the main1 database in the table testbases. It is necessary in a loop to go through all the rows from the Table1 and insert them into query.

SELECT * FROM [DBNAME].[DBO].Table1.Client
UNION

To end up with a big query like this:

SELECT * FROM [DBNAME1].[DBO].Table1.Client
UNION
SELECT * FROM [DBNAME2].[DBO].Table1.Client
UNION
SELECT * FROM [DBNAME3].[DBO].Table1.Client
UNION
SELECT * FROM [DBNAME4].[DBO].Table1.Client
UNION
etc...

How can I do this efficiently and automatically, so I don't need to manually change the query every time we add a client?

Normally, if you have a design with the same table in many databases and you want to use that data in single statements it a sign of a design flaw. If you want to have all this data in one dataset, why are they in different databases/tables? Seems like you shouldn't have a database by client, but a column to denote the client. Otherwise you'll going to need to go down the dynamic SQL approach to make a scalable solution and even your basic statements are only going to be difficult to write and maintain. — Thom A, Apr 26 '21 at 16:15
I know it, but it is a bad task from my university. I did not find information on the Internet on how to correctly loop through a column. — Mauzzz0, Apr 26 '21 at 16:19

WAMLeslie · Accepted Answer · 2021-04-26T16:37:48.187

If all of the tables are identical, you could use sp_MSforeachdb along with an IF EXISTS to go through all of your databases, then insert the table if found into a temp table. You would just need to change the column names from col1, col2, ... to whatever your actual table schema is. Although you'd need to exclude any databases that contain a Table1 table that you don't want included in your data set.

DROP TABLE IF EXISTS #insertTable (col1 varchar(100), col2 varchar(100),...)

DECLARE @command varchar(1000)          
SELECT @command = 'USE ?            
                   EXEC(''IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''Table1'''')         
                            SELECT col1, col2, ... FROM Table1'')' 
            
INSERT INTO #insertTable (col1, col2, ...)          
EXEC sp_MSforeachdb @command

Loop with DB names inserting

1 Answers1