CODEWITHSUNDEEP
sqlsql-server
Navid
Navid

Reputation: 1

SQL Query from a wide range of tables with dynamic table name

I have database with tables containing logs for every month since 2009. Each table name is following this pattern: dbo.LOG[year][month] --> dbo.LOG200909 Now I need to union all these tables to run a query from. Is there any shortcut to do it without union them one by one with hard coding the table name, something like a range of the name that requires less maintenance in future? because these tables are keep adding and I want the query keep working with newly added tables. By the way I am almost new to SQL.

Upvotes: 0

Views: 198

Answers (2)

Jonathan Roberts
Jonathan Roberts

Reputation: 476

DECLARE myCursor cursor 
    FOR SELECT QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME) TABLE_NAME
          FROM INFORMATION_SCHEMA.TABLES T
         WHERE T.TABLE_NAME LIKE 'Log20[0-9][0-9][0-1][0-9]' 
           AND T.TABLE_SCHEMA = 'dbo'
         ORDER BY T.TABLE_NAME;
    
    IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
        DROP TABLE #Results;
    
    CREATE TABLE #Results -- Same structure as Log tables
    (
        Col1 int,
        Col2 nvarchar(20)
        -- ,...
    );
    
    OPEN myCursor
    
    DECLARE @TABLE_NAME sysname
    FETCH NEXT FROM myCursor INTO @TABLE_NAME 
    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @sSql as nvarchar(MAX)
        SET @sSql = 'INSERT INTO #Results(col1,Col2, Col2...) '
                  + 'SELECT * FROM ' + @TABLE_NAME
        EXEC(@sSql);
    
        FETCH NEXT FROM myCursor INTO @TABLE_NAME 
    END
    CLOSE myCursor
    DEALLOCATE myCursor
    
    
    SELECT *
     FROM #Results

Upvotes: 0

potzenhotz
potzenhotz

Reputation: 3

You could use a Stored Procedure:

DROP Procedure IF EXISTS test_procedure;
GO

CREATE PROCEDURE test_procedure
    (@prefix Nvarchar(100),
    @start_year int,
    @start_month int,
    @end_year int,
    @end_month int
    )
AS
BEGIN
    declare @sql varchar(8000)
    declare @year int
    set @year = @start_year + 1
    select @sql='select * from  ' + @prefix + CAST(@start_year as varchar(10))
    WHILE @year <= @end_year
    BEGIN
        select @sql +=' union ' + @prefix + CAST(@year as varchar(10))
        SET @year = @year + 1
    END
    select @sql
    --execute(@sql)
END;
GO

exec test_procedure @prefix = 'abc', @start_year = 2000, @start_month=1, @end_year = 2005, @end_month=1;

This would result in: "select * from abc2000 union abc2001 union abc2002 union abc2003 union abc2004 union abc2005"

Instead of printing the statement you could run it with "execute".

You would need to enhance the loop to calculate the months.

Upvotes: 0

Related Questions