The procedure will check to see if new store information has been added to the wwwStore.stores table. If it is new it will insert the new values into the table. If the store information exists, but is being modified (e.g. new manager) then it will update the row.
I know there is probably a better way to do this, as I am sure this will have to scan to perform the operation, but I would appreciate it if you could comment and/or provide suggestions.
I appreciate your time and support
This is a common problem often referred to as the UPSERT procedure. SQL Server finally addressed the issue in 2008 with the MERGE statement, but alas that doesn't help you here.
There are many solutions to this problem
What you have done so far is a good start, the only change I would make is to just check for the existence of
Another alternative is to introduce a control flag:
this works well when the majority of UPSERTS are
answered Jun 16, 2010 at 04:08 PM
Kev Riley ♦♦
Today there was a question about inserting/updating table from DataTable. Please take a glance at my answer. Here is the link: http://ask.sqlservercentral.com/questions/7307/how-to-update-the-session-datatable-from-c-net-to-sqlserver-2008 If the solution is not acceptable for you (because you have SQL Sever 2005) then the least you can do with your stored procedure is change the contents of the exists. You have
which should be changed to
This is because you simply need an evidence that the record exists or not in order to determine the course of action (update or insert), so there is no need to select anything to find that evidence.
If you would like to change the way it works altogether (switch from the row-level processing to a set-based proc), please let me know and I will append a complete example to this post showing how to achieve it with xml. If you have data in your front end which is represented by some sort of collection of records (DataSet, DataTable, or some custom type) and the total number of records to process at once is within thousands then xml solution might be suitable.
answered Jun 16, 2010 at 04:01 PM
An excellent writeup on UPSERTs is http://www.sqlservercentral.com/articles/T-SQL/61773/.
answered Nov 30, 2010 at 10:33 AM