Insert Update stored proc on SQL Server

by

Your assumption is right, this is the optimal way to do it and it’s called upsert/merge.

Importance of UPSERT – from sqlservercentral.com:

For every update in the case mentioned above we are removing one
additional read from the table if we
use the UPSERT instead of EXISTS.
Unfortunately for an Insert, both the
UPSERT and IF EXISTS methods use the
same number of reads on the table.
Therefore the check for existence
should only be done when there is a
very valid reason to justify the
additional I/O. The optimized way to
do things is to make sure that you
have little reads as possible on the
DB.

The best strategy is to attempt the
update. If no rows are affected by the
update then insert. In most
circumstances, the row will already
exist and only one I/O will be
required.

Edit:
Please check out this answer and the linked blog post to learn about the problems with this pattern and how to make it work safe.

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Solutions for INSERT OR UPDATE on SQL Server
  3. Function vs. Stored Procedure in SQL Server
  4. SQL Server Insert if not exists
  5. SQL Server – How to lock a table until a stored procedure finishes
  6. Stored procedure or function expects parameter which is not supplied
  7. What is a stored procedure?
  8. Search text in stored procedure in SQL Server
  9. SQL Call Stored Procedure for each Row without using a cursor
  10. SQL Server SELECT into existing table
  11. How do I execute a stored procedure once for each row returned by query?
  12. How to check if a stored procedure exists before creating it
  13. Execute Stored Procedure from a Function
  14. SQL: Select dynamic column name based on variable
  15. SQL : in clause in stored procedure:how to pass values
  16. How to return the output of stored procedure into a variable in sql server
  17. How do I pass a list as a parameter in a stored procedure?
  18. Why execute stored procedures is faster than SQL query from a script?
  19. Custom Date/Time formatting in SQL Server
  20. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  21. SQL Server: should I use information_schema tables over sys tables?
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. UNION the results of multiple stored procedures
  24. How to pass a temp table as a parameter into a separate stored procedure
  25. Eliminate and reduce overlapping date ranges
  26. Functions vs Stored Procedures
  27. How to execute a stored procedure inside a select query
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment