Select TOP 1000 across multiple tables

Question

I have a database where I create a new table every day and fill it with data (I know this isn't ideal, but I can't change this). Each table is of the form "TESTdata_xxxxx_DB", where xxxxx is incremented by 1 every day.

I need a simple way to select the top 1000 rows (where a specific condition is met) across many of those tables (i.e. across many dates). For example, I need to query across tables TESTdata_45800_DB, TESTdata_45801, ..., TESTdata_45850_DB.

I have tried the following query, but clearly adding "to" doesn't work, and separating them by comma doesn't combine them the way I want:

SELECT TOP 1000 
    [ItemIndex],
    [Data1],
    [Data2],
    [Data3]     
FROM 
    [TESTDB1].[dbo].[TESTdata_45800_DB] (to...) [TESTdata_45850_DB]
WHERE 
    Data1 LIKE 'High' OR Data1 LIKE 'Medium'
ORDER BY 
    Data1
;

Any help would be appreciated.

Answer 1

A stored procedure using dynamic SQL is a cool approach for this sort of problem. Create a numbers table in memory, use it to create your UNIONed super table, and then execute dynamic SQL to get the result you need:

CREATE PROCEDURE [dbo].[Top1000]
(
    @startDatabaseNumber INT,
    @endDatabaseNumber INT
)
AS
SET NOCOUNT ON

-- inspired by https://stackoverflow.com/a/33146869/7806251
DECLARE @digits TABLE(d INT)
INSERT INTO @digits 
    SELECT 
        d
    FROM 
        (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(d)
 
DECLARE @numbers TABLE(DatabaseNumber VARCHAR(5))       
INSERT INTO @numbers
        SELECT
            LEFT('00000', 5 - LEN(n)) + n AS DatabaseNumber
        FROM
            (
            SELECT 
                CAST(
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)
                    ) AS VARCHAR(10)
                ) AS n
            FROM 
                @digits ones, 
                @digits tens, 
                @digits hundreds,
                @digits thousands,
                @digits tenthousands
            ) x
        WHERE
            n BETWEEN @startDatabaseNumber AND @endDatabaseNumber

DECLARE @unionedTable VARCHAR(MAX) = (      
    SELECT
        STRING_AGG(
            CAST(
                '(
                    SELECT
                        ItemIndex,
                        Data1,
                        Data2,
                        Data3
                    FROM
                        [TESTDB1].[dbo].[TESTdata_'+DatabaseNumber+'_DB]
                )'
            AS VARCHAR(MAX)) -- circumvents STRING_AGG() 8000 character constraint
        , ' UNION ALL ') -- just UNION if you want to de-dupe
    FROM
        @numbers
    )

DECLARE @sql NVARCHAR(MAX) = '
    SELECT TOP 1000 
        *
    FROM 
        ('+@unionedTable+') UnionedTable
    WHERE 
        Data1 IN (''High'', ''Medium'')
    ORDER BY 
        Data1
    ;
    '

-- PRINT @sql -- if you need to debug
EXECUTE sp_executesql @sql

Once this is run and exists, call it with whatever start and end numbers you'd like:

EXEC Top1000 5, 15;

Finally, as other commenters have expressed, I'd suggest not creating a new table each day if you can help it. Inserting each day's data into a running table is more natural and better practice.

Answer 2

If you want a union of tables, meaning all the rows of all the tables dumped into one row set, then use the SQL UNION. For example (this is suggested code, I have not tested it but UNION is very standard SQL and should work everywhere)

SELECT TOP (1000) [ItemIndex],[Data1],[Data2],[Data3]
 from 
   (select [Data1],[Data2],[Data3]
   FROM [TESTDB1].[dbo].[TESTdata_00005_DB] 
   )      
 UNION          
   (select [Data1],[Data2],[Data3]
   FROM [TESTDB1].[dbo].[TESTdata_00006_DB] 
   )      
  UNION
   (select [Data1],[Data2],[Data3]
   FROM [TESTDB1].[dbo].[TESTdata_00007_DB] 
   )      
 WHERE Data1 like 'High' OR Data1 like 'Medium'
  ORDER BY Data1

By the way, though UNION works quite well it is not good performance and in my experience doing a bunch of unions may grow to be difficult to support in the long run. At some point you might consider using alternate data architecture such as a single table but with multiple partitions, one for each day.