問題描述

我有考勤數據庫，每個月都會生成新表像 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.

這篇關于如果存在具有這些名稱的表，則執行聯合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！