问题描述
我有考勤数据库,每个月都会生成新表像 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.