T-SQL to union data from multiple tables dynamically

Question

I have tables with names:

DeviceLogs_1_2023
DeviceLogs_2_2023

Every month new table is added in database.

How can I create a procedure to combine data from multiple tables into one table or view?

select * from DeviceLogs_7_2022
union all
select * from DeviceLogs_8_2022

Answer 1

Any solution relies on the schema staying stable (generally you don't want to use * in views, because funny things happen if you add / drop / rename columns). This specific solution assumes you are on SQL Server 2017 or better (or Azure SQL Edge / Azure SQL Database / Managed Instance). You should always tag the question with the minimum version you need to support.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), 
  CONCAT(N'  SELECT * FROM dbo.', QUOTENAME(name))), N'
  UNION ALL
')
FROM sys.tables
WHERE name LIKE N'DeviceLogs_[0-9]%';

SET @sql = N'CREATE OR ALTER VIEW dbo.SomeViewName
AS
' + @sql;

PRINT @sql;
--EXEC sys.sp_executesql @sql;

• Example db<>fiddle

Answer 2

-- 1. Fisrt of all, make a table like named "CommonDeviceLogs". (I assume you have an union query for old Device logs table).

-- 2. Then dump all the data from your current "DeviceLogs" tables into "CommonDeviceLogs" Table.

-- 3. Now in your SP make a Union Between "CommonDeviceLogs" table and "DeviceLogs" table of current month i.e. like DeviceLogs_2_2024. like shown:

Demo : https://dbfiddle.uk/bjtZLnPW

DECLARE @TableName NVARCHAR(100);
SET @TableName = CONCAT('DeviceLogs_', MONTH(GETDATE()), '_', YEAR(GETDATE()));

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = CONCAT('SELECT * FROM CommonDeviceLogs
UNION
SELECT * FROM ', @TableName);

EXEC sp_executesql @SQLQuery;

-- 4. And make a SQL job which will run on start of every month and dump the data of previous month in "CommonDeviceLogs" table. and Sp for this is:

CREATE PROCEDURE InsertPreviousMonthDataIntoCommonDeviceLogs
AS
BEGIN
    DECLARE @PreviousMonth NVARCHAR(100);
    SET @PreviousMonth = CONCAT('DeviceLogs_', MONTH(DATEADD(MONTH, -1, GETDATE())), '_', YEAR(DATEADD(MONTH, -1, GETDATE())));

    DECLARE @SQLQuery NVARCHAR(MAX);
    SET @SQLQuery = CONCAT('INSERT INTO CommonDeviceLogs SELECT * FROM ', @PreviousMonth);

    EXEC sp_executesql @SQLQuery;
END

I think this approch is better than dynamically selecting all the tables like DeviceLogs_ names every time whenever the SP get called.