Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting the row if it exists and then adding it again.

  • What might be the advantages of deleting over EXISTS or vice-versa?
  • Is there another way of doing this?
share|improve this question
2  
MERGE/UPSERT is what you're after. –  Phil Mar 6 '13 at 13:38
    
Is this for a specific RDBMS? –  JNK Mar 6 '13 at 13:44
    
I'm using sql server 2k8 –  rob Mar 6 '13 at 13:54
1  
Agreed with @Mark - I'm not sure why you would ever want to delete/insert if it already exists. That should be a no-op! –  Aaron Bertrand Mar 6 '13 at 14:32
    
Your friend is not going to be a good source of information while you're learning SQL. Your approach was fine, MERGE is an alternative, delete/insert is nuts. (NB: re-commented as the typo in the original was burning my eyes!) –  Mark Storey-Smith Mar 6 '13 at 18:53

2 Answers 2

up vote 4 down vote accepted

Some people find the code for DELETE-followed-by-INSERT easier to follow especially if some rows are to be UPDATEd as well as some INSERTed, but I would recommend avoiding it. If you do use this method, make sure this or your overall process is wrapped in a transaction so that if something goes wrong nothing happens (or you might delete a row then fail to insert the update).

For large amounts of data, especially when you expect the row(s) to already exist most of the time, DELETE+INSERT can be significanly less efficient: you have one or two row modification operations (DEL+INS instead of INT or UPD) instead of one or zero (INS, UPD, or nothing if the row exists and is correct) that will update the transaction log and data files, and as well as the pages containing the rows you are affecting all relevant index pages will need to be updated too (twice: once for the delete and once for the insert). For small amounts of data this inefficiency issue is not likely to be significant, but keep future scalability in mind. As well as the extra I/O operations that can be immediately measured, you may be increasing heap and/or index fragmentation which can cause other performance problems in the longer term.

In instances where you have a properly defined database with relevant foreign key relations implemented the DELETE operation may simply error due to the rows being referred to by entries in other tables, so the technique definitely can not be universally used. Even worse, if ON DELETE CASCADE is used you could inadvertently delete a great many rows from elsewhere which won't be replaced by your subsequent INSERT. Similar problems can occur if there are triggers set to take action on the table to affect data elsewhere. This is the more important reason not to develop the habbit IMO.

Since SQL2008 the most efficient way to INSERT-or-UPDATE or INSERT-or-NOTHING (or other combinations including DELETEing some rows) is the new MERGE statement. Unlike check+do or delete+insert the structures are only scanned once and the required insert/update/delete operations done in one go as needed. MSSQL's MERGE statement is not dissimilar to (but is more flexible than) what some databases call an UPSERT (update or insert) operation. Similar operations are available in other databases: for example, IIRC, Oracle implements much the same functionality in this respect as MSSQL.

Edit: see the links in Aaron's comment for important caveats to be aware of with MERGE that might make it far less suitable then I suggested.

share|improve this answer
1  
Why do you think MERGE is more efficient? It has the same atomicity problems as separate statements (it's not all "done in one go") and I don't believe you're accurate in that it always only scans once. Never mind that there are a number of unresolved bugs (I highlight a list of about a dozen here). The syntax makes it appear that it is more efficient, and that it is a single atomic statement. See here for proof that it is not. –  Aaron Bertrand Mar 6 '13 at 15:14
2  
I was already a little nervous about using MERGE simply because the syntax is so daunting and unintuitive. Now I stay away from it on purpose. –  Aaron Bertrand Mar 6 '13 at 15:15
    
@Aaron: I must admit I've not used MERGE in productino myself (some of our clietns are stuck on SQL2005 so we restrict ourselves to that feature set for the time being) so I've not had opportunity to run into errors such as those. MERGE not being atomic without "manual" transaction/locking control makes it far less useful looking. –  David Spillett Mar 6 '13 at 16:33
    
You can do it, you just need to set the isolation level correctly, that's all. But for the other reasons it is still no more appealing than the old way, and that's how I'll continue to do it... –  Aaron Bertrand Mar 6 '13 at 16:38
2  
Nice. When there are FKs, besides the "the DELETE operation may simply error" reason, there may be unwanted side effects if ON DELETE CASCADE has been declared ("unwanted" as several (thousand?, million?) records deleted in other tables). –  ypercube Mar 7 '13 at 7:01

As mentioned in the comments...

  • There is no reason to DELETE/INSERT instead of just UPDATE or checking with EXISTS, but there are some reasons NOT to
    • More IO to remove a record and add a new one than an in-row update
    • Depending on clustered index you may increase fragmentation
    • Log file growth
    • Increased locking on the rows in question - EXISTS is about as non-intrusive as you can be, DELETE is the opposite

I'm sure there are many others as well. Checking for existence is in every way superior to deleting an existing record. The act of deleting the existing record REQUIRES A CHECK FOR EXISTENCE, so why on earth would you do anything extra after that?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.