delete records from staging table after they have

Question

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.

here is my stored procedure code

    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

Answer 1

You could run a DELETE statement that cleans out the table

DELETE FROM myTable

My suggestion would be to use BEGIN TRY and BEGIN CATCH so if anything happens you haven't deleted the data from the staging table.

BEGIN TRY
    BEGIN TRAN
    INSERT statement....

    // All successful
    DELETE statement...

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRAN

Answer 2

If things could be happening concurrently (in particular if the staggingtasks can be inserted into while the other process happens), then none of the existing solutions are safe.

You need to Insert the data to a temp table, then insert the data from the temp table and then delete the data from the staging table based on the records in the temp table. This ensures you only deleted the records you inserted and not some others added while the insert happens. And of course you should put all this in a transaction as @Ardman showed.

Further you should not ever do an insert without specifying the fields you are inserting into. Suppose someone recreated the table and rearranged the fields, then your insert would insert into the wrong fields (if the datatypes are compatible) or fail.

SELECT [tour], tourname, [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod], completed , canceled 
INTO #TasksToInsert
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)
                )

INSERT INTO dashboardtasks ([tour], tourname, [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod], completed , canceled )
SELECT [tour], tourname, [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod], completed , canceled 
FROM #TasksToInsert

DELETE FROM staggingtasks  
WHERE EXISTS   (SELECT *
                FROM #TasksToInsert          
                WHERE (staggingtasks.tour=#TasksToInsert.tour 
                        AND staggingtasks.taskname=#TasksToInsert.taskname 
                        AND staggingtasks.deptdate=#TasksToInsert.deptdate 
                        AND staggingtasks.duedate=#TasksToInsert.duedate 
                        AND staggingtasks.tourname=#TasksToInsert.tourname)
                )   

And if this hasn't gone to production yet, please fix the misspelling of StagingTasks. Or it will annoy your developers for years! I would also consider putting in a surrogate key to the dashboardTasks table as a multiple field PK like you have is likely to cause performance issues that a smaller int key would not especially if there are child tables. But still make a unique index on the natural key.

Answer 3

Have a delete statement with the same criteria you have for insert except that you now use EXISTS instead of NOT EXISTS.

Something like this:

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 
                    )
  )


DELETE FROM staggingtasks
 WHERE 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 
                    )
  )

Answer 4

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 

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

The output_table cannot:

• Have enabled triggers defined on it.
• Participate on either side of a foreign key constraint.
• Have CHECK constraints or enabled rules.

Full syntax for your query...

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
                         ))  

Answer 5

You can run multiple queries in a single stored procedure, so unless I'm missing something you should just be able to add

DELETE staggingtasks WHERE <any criteria you need>

after the INSERT query...

Answer 6

Remember that deleting will log everything so in time your log may grow too big. If you don't care about the data in your staging table, then try the TRUNCATE command instead after your insert:

TRUNCATE TABLE staggingtasks

Like others are saying, you may want to put it all into Transactions just in case. The plus about Truncate is that it is fast since no additional logging is required for each record you are purging from the table.