时间戳

首页 > 解决方案 > Delete all records Then what is left put into Temp table

sql - Delete all records Then what is left put into Temp table

问题描述

Please advise. I've got a query (the @variables are local or parameters passed). 

    Delete  a
    From    T_HISTORY a
    JOIN    #tempb ON a.p_no = b.p_no
    Where   a.run_date 
    between Convert(datetime, Convert(varchar, @run_date, 101)) and Convert(datetime, Convert(varchar, @run_date, 101) + ' 23:59:59') 
    and     value_don= @value_don

then we have a second query -- right after

        select  a.*
        into    #test_delete
        From    T_HISTORY  a
        JOIN    #tempb b ON a.p_no = b.p_no
        Where   a.run_date 
        between Convert(datetime, Convert(varchar, @run_date, 101)) and Convert(datetime, Convert(varchar, @run_date, 101) + ' 23:59:59') 
        and     value_don= @value_don

What the hope is -- is to take everything that is in the history table joined on the temp table and delete it if it falls between the run date (00:00 - 23:59:59) and the value_don is equal to our passed parameter.

Whatever is left after whatever wasn't' deleted, and its possible something wasn't. That data should be dropped into a #test_delete

Then the next line of code reads 

    insert into LT_history_Audit_Log (  ... a bunch of columns ... )
    select      *
    From        #test_Delete
    Where       run_date between    Convert(datetime, Convert(varchar, @run_date, 101)) and Convert(datetime, Convert(varchar, @run_date, 101) + ' 23:59:59') 
    and         value_don= @value_don

So theoretically what should happen is we would have an occasional line of code, maybe a few entered into the audit_log table.

However, what i'm seeing happen is that every single line that is deleted in the first statement is entered into the #test_delete table.

The data in the history table is composed of values based on different parameters and run times. The temp table limits what we pull from the history table. Based on this criteria we want to remove these records. This means there could possibly be something in the history table that wasn't removed. We want a record of these records. Based on our data that would normally be a handful of rows at most.

However when the insert into #test_delete happens it inserts every single line that was deleted in the previous statement. Every single line. So if 140 records were deleted (which is our avg) #test_delete has 140 records and they are then entered into the audit table.

The timing isn't the issue here, but i will fix it.

sample data

t_history 
run_date                p_no  .... 
2019-01-10 10:44:47.910 31811 
2019-01-10 10:44:47.910 31817
...

#tempb 
p_no
31817
31818
31819

After the delete statement from the history table for run date 1-10-19, the second line should be deleted where p_no = 31817 and so on. and the first line should remain.

however what appears in #test_delete is both lines, where id = 31817 and 31811. This should not be the case. It's as though the delete statement never happened.

标签： sqlsql-serversql-insertsql-delete

解决方案

您的查询可能会在 23:59:59.001 之后丢失记录（取决于字段格式）

为什么不做

@run_date <= a.run_date 和 a.run_date < dateadd(day,1,@run_date)

我希望您所有名称中带有“日期”的对象都使用日期格式

推荐阅读