locked
SQL Statement - Insert, Update, or both? RRS feed

  • Question

  • 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!

    Wednesday, September 2, 2015 5:49 PM

Answers

All replies

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

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by tsherv Thursday, September 3, 2015 2:06 PM
    Wednesday, September 2, 2015 6:16 PM
  • 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 history)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Wednesday, September 2, 2015 8:47 PM
    • Marked as answer by tsherv Thursday, September 3, 2015 2:06 PM
    Wednesday, September 2, 2015 8:43 PM
  • 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 N Wednesday, September 2, 2015 9:16 PM
    Wednesday, September 2, 2015 8:52 PM
  • 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 needs).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • Edited by pituachMVP Thursday, September 3, 2015 6:44 AM
    Thursday, September 3, 2015 6:40 AM
  • Ronen, I am not sure if I understand your response to my comment.  I suggested that the correct course of action depends on the behavior of his data.  This implies that almost any solution is situational.

    My recommendation was pointed more to assessing the needs and impacts of any solution not to simply break a query in multiple batches.  There are cases where I used MERGE because it was more appropriate to the problem that I was trying to solve.  In other cases, I decided to use separate INSERT, UPDATE, and DELETE statements because it allowed me to develop a better solution to address the concerns at the time.

    This is what the original poster needs to consider.  Although MERGE is able to perform multiple actions against a target table in a single statement, there may be other concerns that need to be considered which could possibly result in a solution better suited without MERGE.

    Thursday, September 3, 2015 1:45 PM
  • Hi J I M B O

    As I said, I agree with you basically :-) 

    There are lot of cases that I prefer to uses separate statements, but as we do not have any more information, the default answer if you need both together update/insert, should be MERGE (if this fits), and I just wanted to clarify this.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Thursday, September 3, 2015 2:03 PM