实现日志表定时生成视图，为实现日志数据可视化分析提供基础

USE [ThreeToOne]

GO

/****** Object:  StoredProcedure [dbo].[WTO_Scan_view1]    Script Date: 01/08/2019 15:23:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    <Author,,Name>

-- Create date: <Create Date,,>

-- Description:   <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[WTO_Scan_view1]

    -- Add the parameters for the stored procedure here

    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

DECLARE @year varchar(4)

DECLARE @month varchar(4)

DECLARE @target varchar(18)

DECLARE @sql varchar(max)

declare @n int

declare @n0 int

SET @n0 = 2018

SET @n = datepart(YYYY,getdate())+1

declare @j int

declare @j0 int

declare @n1 varchar(max)

set @n1=''

begin

while @n0<@n

 begin

    if @n0=2018

     BEGIN

        SET @j0 = 36

        SET @j = 53

      END

    else

    BEGIN

        SET @j0 = 1

        SET @j = datepart(week,getdate())+1

        END

    while @j0<@j+1

        BEGIN

        SET @year = @n0

        SET @month=replace(right(str(@j0),4),' ','0')

        SET @target= 'ScanDo_' + @year  + @month

        set @n1=@n1+' union all SELECT [ID],[FACode],[SNCode],[BiCode],[MDCode],[Location],[CarNum],[LocalNum],[FlagFC],[FlagCL],[UserID],[LogonMAC],[workname],[LogonTime], ''' + @target + ''' AS TabName

              FROM    [dbo].[' + @target + ']'

        set @j0=@j0+1

       end

     set @n0=@n0+1

  end

set @n1=SUBSTRING (@n1,12,LEN(@n1)-11)

set @sql='ALTER view Scan_view1 as

'+@n1+''

--SELECT  @sql

--print @sql

 exec (@sql)

end