如果存在具有这些名称的表，则执行联合

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我有考勤数据库，每个月都会生成新表像 TRNS0419,TRNS0519,TRNS0619,TRNS0719.为了合并数据，我使用了 Union.但是每个月我都手动输入表名，有没有办法在生成新表时自动选择数据，例如 TRNS0819

I have attendance database where every month new table get generated like TRNS0419,TRNS0519,TRNS0619,TRNS0719.To combine data i have used Union. But every month i have enter table name manually, Is there any way if automaticalls picks data when ever new table gets generated such TRNS0819

我尝试过使用 union all，但它没有使用不存在的表.

I have tried using union all but its isnt taking table which is not present.

Select * from TRNS0419 union all Select * from TRNS0519
union all Select * from TRNS0619 union all Select * from TRNS0719

我的查询没有采用 union all Select * from TRNS0819 因为这在 db 中不可用

my query is not taking union all Select * from TRNS0819 because this isn't available in db

它应该组合所有表并在查找临时表中显示结果.请帮忙

It should combine all tables and show result in find temp table. Please help

推荐答案

将以下代码包装在存储过程中:

Wrap the following code in stored procedure:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

SELECT @DynamicTSQLStatement = STUFF
(
    (
        SELECT N' UNION ALL SELECT * FROM ' + '[' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']'
        FROM [sys].[tables]
        WHERE [name] LIKE 'TRNS%'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,10
    ,''
);

EXEC sp_executesql @DynamicTSQLStatement;

当从 [sys].[tables] 视图中提取表名时，您可以添加更多过滤器.

You can add more filters when table name is extracted from the [sys].[tables] view.

相关文章