declare @time1 varchar(19) = convert(varchar(10),getdate(),120)+' 00:00:00'--插入[RecordTime]
--declare @time1 cast(convert(varchar(10),getdate(),120)+' 00:00:00' as datetime)
declare @time2 varchar(19) = convert(varchar(10),dateadd(day,0,getDate()),20)
print @time2
declare @time3 varchar(19) = convert(varchar(10),dateadd(day,-1,getDate()),20)
declare @count int
declare @i int = 1
declare @FactoryCode int
declare @MillCode int
declare @MaterialCode int
declare @LabWaterRate float
declare @rows int --作为临时表的行遍历取数据所用
select * into #temptab1 from (select [FactoryCode],[MillCode],[MaterialCode],[LabWaterRate],ROW_NUMBER() over(order by [MaterialCode]) as rows from [dbo].[PM_LabWaterAgencyDay] where [RecordTime] = @time3) as temptab
select @count = COUNT(*) from #temptab1
--select * from #temptab1
while(@i <= @count)
begin
select @FactoryCode = [FactoryCode],@MillCode = [MillCode],@MaterialCode = [MaterialCode] from #temptab1 where rows = @i
if not exists(select * from [dbo].[PM_LabWaterAgencyDay] where [RecordTime] = @time2 and [FactoryCode] = @FactoryCode and [MillCode] = @MillCode and [MaterialCode] = @MaterialCode)
--如果昨天此物料有水分,今天没有的话,执行插入语句,将昨天的数据插入
--如果今天已经录入了水分,跳过判断@i+1进入下一个循环
begin
print '今天没有录入数据,将昨天的水分插到今天'
INSERT INTO [dbo].[PM_LabWaterAgencyDay] VALUES (@time1,@FactoryCode,@MillCode,@MaterialCode,@LabWaterRate,'admin',GETDATE())
end
set @i = @i +1
end
drop table #temptab1
需求:如果表中今天没有录入数据,那么就将昨天表中的数据插入到今天表中,如果今天的表中有数据那么,不能覆盖今天的