Menu
I have an application I’m writing in access with a SQL server backend. One of the most heavily used parts is where the users selects an answer to a question, a stored procedure is then fired which sees if an answer has already been given, if it has an UPDATE is executed, if not an INSERT is executed.
This works just fine but now we have upgraded to SQL server 2008 express I was wondering if it would be better/quicker/more efficient to rewrite this SP to use the new MERGE command.
Does anyone have any idea if this is faster than doing a SELECT followed by either an INSERT or UPDATE?
415
A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.
The term upsert is a portmanteau – a combination of the words “update” and “insert.” In the context of relational databases, an upsert is a database operation that will update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn't already exist.
You can efficiently update and insert new data by loading your data into a staging table first. Amazon Redshift doesn't support a single merge statement (update or insert, also known as an upsert) to insert and update data from a single data source. However, you can effectively perform a merge operation.
Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more. Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row.
Not worth the effort. Possibly doable, but it will not give you anything noticable.
MERGE is especially targeting data warehouses where finding out what to insert/update is the tricky part. It allows all operations (insert, update) to be done with ONE set of merges, compared to one for each condition. This makes no real difference in your case.
I have one database where I am uploading 3-5 million rows into a 300 million row table - there merge improoves my performance by 50% (one table scan instead of two).
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
