Only inserting a row if it’s not already there

by

What about the “JFDI” pattern?

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes.
What if the UPDLOCK is escalated and the whole table is locked?

Read lesson 4:

Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…) line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.

More Related Contents:

  1. SET NOCOUNT ON usage
  2. SQL Server – How to lock a table until a stored procedure finishes
  3. Unpivot with column name
  4. Selecting data from two different servers in SQL Server
  5. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  6. SQL JOIN vs IN performance?
  7. Most efficient way in SQL Server to get date from date+time?
  8. Convert varchar into datetime in SQL Server
  9. TSQL Pivot without aggregate function
  10. How to generate a range of dates in SQL Server
  11. SQL RANK() versus ROW_NUMBER()
  12. Generate SQL Create Scripts for existing tables with Query
  13. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  14. How do I delete from multiple tables using INNER JOIN in SQL server
  15. Check if a row exists, otherwise insert
  16. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  17. How do I perform a GROUP BY on an aliased column in SQL Server?
  18. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  19. CTE error: “Types don’t match between the anchor and the recursive part”
  20. How do I remove extended ASCII characters from a string in T-SQL?
  21. Getting Dates between a range of dates
  22. The most elegant way to generate permutations in SQL server
  23. Using SQL Server as a DB queue with multiple clients
  24. SQL Server search using like while ignoring blank spaces
  25. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  26. Compare dates in T-SQL, ignoring the time part
  27. How do I compare two columns for equality in SQL Server?
  28. Find last sunday
  29. SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns
  30. Removing duplicate rows (based on values from multiple columns) from SQL table

Leave a Comment