将记录添加到实际表后从临时表中删除记录 [英] delete records from staging table after they have been added to the real table

问题描述

我有一个 asp.net 程序，它将任务添加到我的临时表并执行一个存储过程，该存储过程接受这些任务并将它们放在我的实际表中，我想在添加记录后删除它们，因为我的临时表桌子很快就会变大.

I have an asp.net program, that adds tasks to my staging table and executes a stored procedure that takes these tasks and places them in my actual table, i would like to delete the records after they are added, because my staging table will get too big fast.

这是我的存储过程代码

    INSERT INTO dashboardtasks
SELECT [tour], tourname, [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod], completed , canceled
FROM staggingtasks
WHERE NOT EXISTS(SELECT * 
                 FROM dashboardtasks 
                 WHERE (staggingtasks.tour=dashboardtasks.tour and
                       staggingtasks.taskname=dashboardtasks.taskname and 
staggingtasks.deptdate=dashboardtasks.deptdate and 
staggingtasks.duedate=dashboardtasks.duedate and
staggingtasks.tourname=dashboardtasks.tourname 

)
                 )




END

推荐答案

也许您可以从临时表中结合 OUTPUT 子句执行 DELETE.并将 OUTPUT 子句的结果 INSERT 插入到主表中，以便在一个原子语句中完成所有操作.

Possibly you could do the DELETE from your staging table combined with the OUTPUT clause. and INSERT the result of the OUTPUT clause into your main table to do this all in one atomic statement.

OUTPUT deleted.* into dashboardtasks 

BOL 中列出了一些限制 虽然这可能会使这种方法不可行.

There are some restrictions listed in BOL though which may make this approach unviable.

输出表不能:

• 在其上定义了启用的触发器.
• 参加任何一方外键约束.
• 检查一下约束或启用的规则.

查询的完整语法...

DELETE FROM staggingtasks
OUTPUT DELETED.[tour],
       DELETED.tourname,
       DELETED.[taskname],
       DELETED.[deptdate],
       DELETED.[tasktype],
       DELETED.[desc],
       DELETED.[duedate],
       DELETED.[compdate],
       DELETED.[comments],
       DELETED.[agent],
       DELETED.[compby],
       DELETED.[graceperiod],
       DELETED.completed,
       DELETED.canceled
INTO dashboardtasks
WHERE  NOT EXISTS(SELECT *
                  FROM   dashboardtasks
                  WHERE  ( staggingtasks.tour = dashboardtasks.tour
                           and staggingtasks.taskname = dashboardtasks.taskname
                           and staggingtasks.deptdate = dashboardtasks.deptdate
                           and staggingtasks.duedate = dashboardtasks.duedate
                           and staggingtasks.tourname = dashboardtasks.tourname
                         ))  

这篇关于将记录添加到实际表后从临时表中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！