sql server 2008 - SQL MERGE statement to update data -

i've got table data named energydata

it has 3 columns

(webmeterid, datetime, kwh) 

i have new set of updated data in table temp_energydata.

the datetime , webmeterid stay same. kwh values need updating temp_energydata table.

how write t-sql correct way?

assuming want actual sql server merge statement:

merge dbo.energydata (holdlock) target using dbo.temp_energydata source     on target.webmeterid = source.webmeterid     , target.datetime = source.datetime when matched      update set target.kwh = source.kwh when not matched target     insert (webmeterid, datetime, kwh)     values (source.webmeterid, source.datetime, source.kwh); 

if want delete records in target aren't in source:

merge dbo.energydata (holdlock) target using dbo.temp_energydata source     on target.webmeterid = source.webmeterid     , target.datetime = source.datetime when matched      update set target.kwh = source.kwh when not matched target     insert (webmeterid, datetime, kwh)     values (source.webmeterid, source.datetime, source.kwh) when not matched source     delete; 

---

because has become bit more popular, feel should expand answer bit caveats aware of.

first, there several blogs report concurrency issues merge statement. can largely worked around specifying holdlock or serializable lock hint:

merge dbo.energydata (holdlock) target [...] 

you can accomplish same thing more restrictive transaction isolation levels.

there several other known issues merge. can tell, of them not common problems or can worked around same locking hints above, haven't tested them.

as is, though i've never had problems merge statement myself, use with (holdlock) hint now, , prefer use statement in straightforward of cases.