Hi,

I'm working on a project where I have a SQL DB for a Work Order system.  I've created a custom table that holds data for open work orders.  I've scheduled a "Job" using the SQL server agent that runs a Insert (where not exist) statement every 5 minutes.  It works fine to insert new work orders into the table.  The problem I have is how to get ride of the work orders once they are completed. 

Do I need to use 2 different SQL statements in my SQL Job (maybe a second step) to cleanup all closed work orders?  Am I on the right track using 2 SQL statements to insert new work orders and cleanup closed work orders?

Thanks!

You can probably use one MERGE command to do both operations at once. Or you can run 2 separate statements.

As Naomi said you can use MERGE statement for Inserting, Updating, and Deleting Data in one statement. For more information on how to use it and for example you can check this link:

https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Or this one:

https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396

** For most cases I do not think that you should delete any row once the order completed, but only update it and marked as completed. You should keep history if you can (once you delete data you can not retrieve it and you do know now what you might need in the future... for example someone will want to see statistics or just his

I suppose that it really depends on the behavior of your data.  If the INSERT, DELETE, or UPDATE statements run disproportionally long, then it may be better to have them as separate statements.  You can run each on smaller data sets more frequently.

Otherwise, if the MERGE statement is lightning quick and you don't need to execute the individual INSERT, UPDATE, or DELETE commands, you can go with a MERGE.

• Proposed as answer by Naomi NModerator 9 hours 52 minutes ago

That is true (THEORETICALLY)... but

Everything in SQL Server is case-by-case :-)

But using MERGE in most cases use much less resources that using two statements INSERT+UPDATE, since you do not need to scan the table twice.

This is like saying to someone that use simple query "update TableX set col = 'new' where id>0 and id<100" that he should use 2 queries  "update TableX set col = 'new' where id>0 and id<50" and "update TableX set col = 'new' where id>49 and id<100"

Yes, in some cases this is better to split queries to batches :-)
But this should not be the default in most cases (if this fit the n