实现日志表定时生成视图，为实现日志数据可视化分析提供基础
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
