What is the best way to get rows from different databases?

Question

I have 10 identical databases.

I get the database names at runtime.

I want to store rows into a collection of objects.

I also only want one hit on the database server.

My current approach:-

• In a query (no stored procedures for X reason) I get list of databases and store in a temporary table.

• Then I iterate through each database and create a dynamic query and execute it.

  DECLARE @MaxRownum int SET @MaxRownum = (SELECT MAX(RowNum) FROM #Databases) 
  DECLARE @Iter int SET @Iter = 1
  WHILE @Iter <= @MaxRownum
  BEGIN
  DECLARE @Database varchar(255) SELECT @Database = Databases FROM #Databases 
        WHERE RowNum = @Iter
  IF HAS_DBACCESS(@Database) > 0
      BEGIN 
        //appending query
  END
  SET @Iter = @Iter + 1
  END
  EXEC(@Query)
  

Can I use Linq + entity framework with one hit to server, without dynamic query and without hampering the performance? Is there any better solution?

Answer 1

Having no idea what your query is (I asked but you did not supply it), and not sure that you understand it is going to be extremely difficult to supply database names as variables without a "dynamic query", here is a much simpler way to do it IMHO:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'UNION ALL'
    --// you will need to fill in your "//appending query" stuff here:
    + ' SELECT ... FROM ' + QUOTENAME(Databases) + '.dbo.tablename'
 FROM #Databases
 WHERE HAS_DBACCESS(Databases) = 1;

SET @sql = STUFF(@sql, 1, 9, '');

EXEC sp_executesql @sql;