自动插数 2-优快云博客

本文介绍了一种基于SQL的定时任务实现方案，通过创建存储过程来动态生成并执行特定时间段内的数据插入任务。该方案根据不同工作制度和时间条件，如三班制、白班制等，动态选择需要插入数据的表，并执行相应的数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--exec dbo.p_InsertNR_YXRZUserTable

CREATE proc dbo.p_InsertNR_YXRZUserTable
AS
--tbYXRZ_name,Flag:0　三班制,1 白班制,2 全天制后夜, 3 每周制, 4 每月制, 5 全天制前夜, 6 全天制白班
--tbYXRZ_name,FlagOther:周,或月的第几天
DECLARE @time char(5),@TableName varchar(8000),@TableName1 varchar(100),@i int,@date char(10),@bc char(1)
DECLARE @PartDay char(2),@PartWeek char(1)
SELECT @tablename=''
SET @i = 0
SELECT @time = substring(cONvert(varchar,getdate(),20),12,5)
SELECT @PartDay = DatePart(Day,GetDate()),@PartWeek = DatePart(WeekDay,GetDate())

------------------------------
SELECT @TableName = @TableName + b.TableName+','
FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 0
SELECT @i = @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 0

------------------------------
SELECT @TableName = @TableName + b.TableName+','
FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_Name = '燃料翻车机日志'
SELECT @i = @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_Name = '燃料翻车机日志'

------------------------------
IF (@time < '09:00') AND (@time >= '08:00') BEGIN
    SELECT @TableName = @TableName + b.TableName +','
      FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 1
    SELECT @i = @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 1
END;

------------------------------
IF (@time < '02:00') AND (@time >= '00:00') BEGIN
    SELECT @TableName = @TableName + b.TableName +','
      FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 2
    SELECT @i = @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 2
----每月的预定日期的后夜生成
--/*
    SELECT @TableName = @TableName + b.TableName +','
      FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 4 AND a.FlagOther = @PartDay - 1
    SELECT @i= @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 4 AND a.FlagOther = @PartDay - 1
--*/
END;

------------------------------
IF (@time < '17:00') AND (@time >= '16:00') BEGIN
    SELECT @TableName = @TableName + b.TableName +','
      FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 5
    SELECT @i = @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 5
END;

------------------------------
SELECT @TableName = @TableName + b.TableName +','
FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 3 AND a.FlagOther = @PartWeek - 1
SELECT @i= @i + count(1) FROM tbyxrz_name a INNER JOIN tbYXRZNoteModule b ON a.rz_id= b.rz_id WHERE a.rz_isInsert = 0 AND a.Flag = 3 AND a.FlagOther = @PartWeek - 1

------------------------------

--print @TableName
SELECT @date = convert(char(10),getdate(),20)
SELECT @bc= (CASE WHEN @time>'08:19' AND @time <'16:19' THEN '0'
WHEN @time>'00:19' AND @time <'08:19' THEN '2' ELSE '1' END)

WHILE @i>0 BEGIN
    SET @TableName1 = substring(@TableName,1,charindex(',',@TableName)-1)

    EXEC( 'insert into '+ @TableName1 +'(repORtdate,bc,tq,CreateTime,data)
      SELECT cONvert(char(10),getdate(),20),bc='+@bc+',''0'',getdate(),data
      FROM tbYXRZNoteModule WHERE tablename='''+@TableName1 + '''')

EXEC('update a SET a.bz = b.bz FROM '+@TableName1+
' a left outer JOIN tbYXzb b ON a.repORtdate=b.time1 AND a.bc=b.bc WHERE a.repORtdate='''+@date+'''')

SELECT @TableName = stuff(@TableName,1,charindex(',',@TableName),'')
SET @i = @i - 1
END