Update statement for sql server 2008

This table is known as the source table. The records in the NewCustomers table will be used to update and insert records in to the Customers table. The first record in the NewCustomers table is going to be used to update the single existing Customers record, where as the second record in the NewCustomers table is going to be inserted into the Customers table.

In order to identify which records are updates and which ones are inserts I need to identify a key column to match on between the Customers and NewCustomers table.

Here I am joining the source and target tables based on the LastName column in source and target tables. To show how this works let me go through an example. My example will use the MERGE statement to maintain a student roster for the students enrolled in the current year. My student table needs to be updated every year with a list of all the new students as well as the returning students.

To do this I need a source table that will contain those records that need to be inserted, or updated. Here is the code to create and populate my source table:. My merge example will be using the source table NewYearRoster to maintain the target table Students by inserting new students, updating students information if they were enrolled last year meaning already in Student table , and delete student records if they are not enrolled for the new year meaning not in NewYearRoster table. Below I have modified my above simple insert, update, or delete example to only update existing records if the address in the NewYearRoster is different from the current Students address record:.

Now you can tell this statement performed 4 different actions, 1 insert, 2 updates and 1 delete statement. Below I have modified my basic insert, update, and delete example to contain a TOP clause to request that only 2 actions be taken:. If you play with this statement you will find the updates, are performed first, then inserts, and lastly delete statements will be performed. Now with a single statement you can easily implement insert, update, and delete logic to handle criteria for maintaining a table.

Learn more. Asked 8 years, 6 months ago. Active 8 years, 6 months ago. Viewed 4k times. I have a table to update. Not quite sure how to structure that solution GilM - do you have a syntactically correct version?

I've added an answer below. Add a comment. Active Oldest Votes. GilM GilM 3, 16 16 silver badges 15 15 bronze badges. EDIT: I wasn't very explicit before. John Tseng John Tseng 6, 2 2 gold badges 24 24 silver badges 33 33 bronze badges. DanSnell Thanks for the comment. I've also updated the fiddle with more queries showing the exact same execution plans to back up my claim.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. Archived Forums. Sign in to vote. KID , D. ParentID , D. SGS , D. ParentID , H. NewSGS , H. KID update D set D. KID where D. Monday, November 30, AM. KID, D. ParentID, D. SGS, D. PS, D. HID like H. KID, H.