我需要创建一个视图，根据它们的前缀联合所有表(每月添加新表) [英] I need to create a view that unions all tables based on their prefix (new tables added monthly)

问题描述

我的客户正在使用的集成软件从 RMS 系统中提取 ISAM 文件并创建许多具有相同架构的表，但他们使用的软件不支持将它们合并在一起.

The integration software that my client is using is pulling over ISAM files from a RMS system and creating lots of tables that all have identical schemas, but nonetheless the software they use doesn't support merging them together.

以下是一些示例表名称:

Here's some example table names:

• SOME_TABLE_XYZ_2012OCT
• SOME_TABLE_ABC_2012OCT
• SOME_TABLE_LMN_2012OCT
• SOME_TABLE_XYZ_2012NOV
• SOME_TABLE_ABC_2012NOV
• SOME_TABLE_LMN_2012NOV

我需要将所有这些汇总到一个名为 SOME_TABLE 的视图中，并将数据联合放在一起.它必须是动态的，因为可以有新的 3 个字符代码，而且很明显，每个月都会创建新表.

I need to roll these all up into a single view named SOME_TABLE, with the data union all'd together. It must be dynamic because there can be new 3 char codes and obviously, monthly new tables being created.

理想情况下，我想要一个创建名为 SOME_TABLE 的视图的 SP，然后我可以查询 SOME_TABLE 来完成我需要的工作(将 ETL 安排到 sql server 2012 数据仓库中).我知道我可以让 SP 返回一个游标，但是如果我需要性能和其他 ETL 工具的易用性，创建视图可以让我灵活地将其设为索引视图.

Ideally I'd like a SP that creates a view named SOME_TABLE, then I can query off of SOME_TABLE to do the work that I need on it (scheduled ETL into a sql server 2012 data warehouse). I know that I could just have the SP return a cursor, but creating the view gives me flexibility to make it an indexed view if I need for performance, and ease of use from other ETL tools.

我想查询 sys.tables(或其他任何内容)，然后从那里开始为创建视图构建 sql 语句.

I figure on querying sys.tables (or whatever it is), and working from there to build up a sql statement for a create view.

关于如何解决这个问题还有其他好的想法吗?

Any other bright ideas on how to approach this?

推荐答案

您可以使用此查询批处理来创建视图.但你需要不断更新它.

You can use this query batch to create the view. But you need to keep updating it.

declare @v nvarchar(max) =
    (
        select stuff((
        select cast(' union all select * from ' as nvarchar(max)) + quotename(name)
          from sys.tables
         where name like 'SOME\_TABLE\____\_[0-9][0-9][0-9][0-9][a-Z][a-Z][a-Z]' escape '\'
           for xml path('a'), type
        ).value('.','nvarchar(max)'),1,11,'')
    );
set @v = 'CREATE VIEW SOME_TABLE AS ' + @v;
exec (@v);

这是一个存储过程，它接受基表名称，并为其创建一个视图(我将上面的代码包装到一个接受参数的过程中)

This is a stored proc that takes the base table name, and creates a view for it (I wrapped the above code into a proc that takes a parameter)

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE spCreateUnionedView
        @BaseTableName varchar(100)
    AS
    BEGIN
        SET NOCOUNT ON;

        declare @v nvarchar(max) =
        (
            select stuff((
            select cast(' union all select * from ' as nvarchar(max)) + quotename(name)
              from sys.tables
             where name like replace(@BaseTableName, '_', '\_') + '\____\_[0-9][0-9][0-9][0-9][a-Z][a-Z][a-Z]' escape '\'
               for xml path('a'), type
            ).value('.','nvarchar(max)'),1,11,'')
        );

        declare @s nvarchar(max) = 'DROP VIEW ' + @BaseTableName;
        exec (@s);

        set @v = 'CREATE VIEW ' + @BaseTableName + ' AS ' + @v;
        exec (@v);

    END
    GO

这篇关于我需要创建一个视图，根据它们的前缀联合所有表(每月添加新表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！