Query multiple tables in different database with the same name

Question

I have multiple databases and their names are in masterDBTable.

MasterDBTable

ID DBName

CustomerDB1
CustomerDB2
CustomerDB3

In each of these DB's, there's a table called clients

How would I loop through and insert all the clients from each DB into a temp table? When I try I get the following error:

"variable assignment is not allowed in a cursor declaration."

Here's an example:

DECLARE @DataBaseName VARCHAR
    ,@SQL NVARCHAR

CREATE TABLE #TempClient (ClientName VARCHAR(50))

DECLARE clients CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT DISTINCT @DataBaseName = DataBaseName
FROM AIMS.DataBaseMaster

OPEN clients

FETCH NEXT
FROM clients
INTO @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'INSERT INTO #TempClient(ClientName)
     SELECT ClientName FROM ' + @DataBaseName + '.[Example].[Clients]'

    EXECUTE sp_executesql @SQL
END

CLOSE clients

DEALLOCATE clients

SELECT *
FROM #TempClient

zip · Accepted Answer · 2020-02-07 23:34:37Z

1

You need to remove the variable here:

SELECT DISTINCT  @DataBaseName = DataBaseName
          FROM AIMS.DataBaseMaster

to

SELECT DISTINCT  DataBaseName
          FROM AIMS.DataBaseMaster

Also

after

EXECUTE sp_executesql @SQL

add

   FETCH NEXT FROM clients INTO @DataBaseName

to get the next element in the cursor

answered Feb 7, 2020 at 23:34

zip

4,0612 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query multiple tables in different database with the same name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related