在视图中连接具有各种后缀的历史表

Question

在我的一个较大的表中，旧记录被移动到以季度和年份为后缀的单独的表中。例如，SILO_q1_2014

对于历史查询，我通常使用包含当前表和所有历史表的视图。这将包括上一季度，例如q2_2014，但一旦我们进入到2014年第四季度，新的q3_2014表就会过时。

，那么，让这个视图与最新季度表保持最新的最佳方式是什么呢？

ALTER VIEW [dbo].[v_SILO]
AS
    SELECT     *
    FROM         dbo.SILO
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q2_2014
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q1_2014
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q4_2012
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q3_2012
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q2_2012
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q1_2012
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q4_2011
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q3_2011
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q2_2011
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q1_2011
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q4_2010
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q3_2010
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q2_2010
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q1_2010
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q4_2009
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q3_2009
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q2_2009
    WHERE     active_flag = 1
    UNION ALL
    SELECT     *
    FROM         dbo.SILO_q1_2009
    WHERE     active_flag = 1

GO

注意:这是在SQL 2000 db中。

Answer 1

假设您有一个标准的命名约定，您可以创建一个存储的proc，它将删除并重新创建视图，以包括当前季度。然后，将存储的proc作为一个计划作业放在SQL Server中，每月运行一次。