SQL Server：实现自动包含新增同结构年度表的Union All视图或可像视图使用的存储方案问询

Nice question! Given that you can't consolidate all these yearly tables into a single table, here are a few practical approaches to achieve your goal of dynamically including new yearly tables in your combined dataset—either via an auto-updatable view or a solution that works just like a view for queries:

1. Dynamic View Update via Stored Procedure (Recommended) #

This approach creates a stored procedure that automatically detects all tableX_YYYY tables, builds the UNION ALL query dynamically, and updates a view to include all matching tables. The resulting view behaves exactly like a regular view—you can use it in CTEs, join it with other tables, etc.

Step 1: Create the Update Procedure #

CREATE PROCEDURE dbo.Refresh_TableX_Combined_View
AS
BEGIN
    SET NOCOUNT ON;

    -- Build the UNION ALL query by finding all matching yearly tables
    DECLARE @DynamicSQL NVARCHAR(MAX) = N'';
    
    SELECT @DynamicSQL = @DynamicSQL + 
        N'SELECT * FROM ' + QUOTENAME(t.name) + N' UNION ALL '
    FROM sys.tables t
    WHERE t.name LIKE N'tableX_[0-9][0-9][0-9][0-9]' -- Matches tableX_YYYY format
    ORDER BY t.name;

    -- Remove the trailing "UNION ALL "
    IF LEN(@DynamicSQL) > 0
        SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10);

    -- Create or alter the view with the dynamic query
    SET @DynamicSQL = N'CREATE OR ALTER VIEW dbo.vw_TableX_All_Years AS ' + @DynamicSQL;

    -- Execute the dynamic SQL to update the view
    EXEC sp_executesql @DynamicSQL;
END;

Step 2: Use the View and Update It #

• First run the procedure to create the initial view:

  EXEC dbo.Refresh_TableX_Combined_View;
  

• Now you can query the view just like any other table/view:

  WITH cte AS (
      SELECT * FROM dbo.vw_TableX_All_Years WHERE Column1 = 123
  )
  SELECT * FROM cte JOIN OtherTable ot ON cte.ID = ot.TableX_ID;
  

• Whenever a new yearly table (like tableX_2022) is added, just re-run the stored procedure to update the view automatically. You can even set up a SQL Agent Job to run this procedure periodically (e.g., once a year in January) to make it fully hands-off.

2. Multi-Statement Table-Valued Function #

If you want a solution that doesn't require manually updating a view, you can use a table-valued function. This acts like a virtual table and can be used in queries just like a view, but note that performance may be slower than a view for large datasets, and you'll need to define the output schema explicitly.

Create the Function #

CREATE FUNCTION dbo.fn_TableX_All_Years()
RETURNS @CombinedData TABLE (
    -- Explicitly define all columns matching the tableX_YYYY schema
    ID INT,
    DataColumn1 VARCHAR(100),
    DataColumn2 DATETIME,
    -- Add all other columns from your yearly tables here
)
AS
BEGIN
    DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

    SELECT @DynamicSQL = @DynamicSQL + 
        N'INSERT INTO @CombinedData SELECT * FROM ' + QUOTENAME(t.name) + N'; '
    FROM sys.tables t
    WHERE t.name LIKE N'tableX_[0-9][0-9][0-9][0-9]'
    ORDER BY t.name;

    -- Execute the dynamic insert to populate the result table
    EXEC sp_executesql @DynamicSQL, 
        N'@CombinedData TABLE (ID INT, DataColumn1 VARCHAR(100), DataColumn2 DATETIME) OUTPUT',
        @CombinedData OUTPUT;

    RETURN;
END;

Use the Function #

Query it like any table:

SELECT * FROM dbo.fn_TableX_All_Years() WHERE DataColumn2 > '2020-01-01';

It works in CTEs and joins too, but remember to update the function's output schema if the yearly tables' structure ever changes.

3. Partitioned View (Not Ideal for Dynamic Scenarios) #

SQL Server has built-in partitioned views for combining identical-structured tables, but this requires adding CHECK constraints to each yearly table (e.g., CHECK (YearColumn = 2016) for tableX_2016) and manually updating the view's UNION ALL list when new tables are added. Since it doesn't auto-detect new tables, it's not the best fit for your requirement, but it's worth mentioning as an official alternative for static yearly tables.

内容的提问来源于stack exchange，提问作者Tai