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