I've got a table with data named energydata
it has just three columns
(webmeterID, DateTime, kWh) I have a new set of updated data in a table temp_energydata.
The DateTime and the webmeterID stay the same. But the kWh values need updating from temp_energydata table.
How do I write the T-SQL for this the correct way?
Assuming you want an actual SQL Server MERGE statement:
MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target USING dbo.temp_energydata AS source ON target.webmeterID = source.webmeterID AND target.DateTime = source.DateTime WHEN MATCHED THEN UPDATE SET target.kWh = source.kWh WHEN NOT MATCHED BY TARGET THEN INSERT (webmeterID, DateTime, kWh) VALUES (source.webmeterID, source.DateTime, source.kWh); If you also want to delete records in the target that aren't in the source:
MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target USING dbo.temp_energydata AS source ON target.webmeterID = source.webmeterID AND target.DateTime = source.DateTime WHEN MATCHED THEN UPDATE SET target.kWh = source.kWh WHEN NOT MATCHED BY TARGET THEN INSERT (webmeterID, DateTime, kWh) VALUES (source.webmeterID, source.DateTime, source.kWh) WHEN NOT MATCHED BY SOURCE THEN DELETE; Because this has become a bit more popular, I feel like I should expand this answer a bit with some caveats to be aware of.
First, there are several blogs which report concurrency issues with the MERGE statement in older versions of SQL Server. I do not know if this issue has ever been addressed in later editions. Either way, this can largely be worked around by specifying the HOLDLOCK or SERIALIZABLE lock hint:
MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target [...] You can also accomplish the same thing with more restrictive transaction isolation levels.
There are several other known issues with MERGE. (Note that since Microsoft nuked Connect and didn't link issues in the old system to issues in the new system, these older issues are hard to track down. Thanks, Microsoft!) From what I can tell, most of them are not common problems or can be worked around with the same locking hints as above, but I haven't tested them.
As it is, even though I've never had any problems with the MERGE statement myself, I always use the WITH (HOLDLOCK) hint now, and I prefer to use the statement only in the most straightforward of cases.
NOT MATCHED BY SOURCE clause may need to be used with caution in this case. If temp_energydata contains updates for only a subset of members in energydata, your second MERGE is going to delete the data of all members not found in the temporary set.
I often used Bacon Bits great answer as I just can not memorize the syntax.
But I usually add a CTE as an addition to make the DELETE part more useful because very often you will want to apply the merge only to a part of the target table.
WITH target as ( SELECT * FROM dbo.energydate WHERE DateTime > GETDATE() ) MERGE INTO target WITH (HOLDLOCK) USING dbo.temp_energydata AS source ON target.webmeterID = source.webmeterID AND target.DateTime = source.DateTime WHEN MATCHED THEN UPDATE SET target.kWh = source.kWh WHEN NOT MATCHED BY TARGET THEN INSERT (webmeterID, DateTime, kWh) VALUES (source.webmeterID, source.DateTime, source.kWh) WHEN NOT MATCHED BY SOURCE THEN DELETE 
If you need just update your records in energydata based on data in temp_energydata, assuming that temp_enerydata doesn't contain any new records, then try this:
UPDATE e SET e.kWh = t.kWh FROM energydata e INNER JOIN temp_energydata t ON e.webmeterID = t.webmeterID AND e.DateTime = t.DateTime Here is working sqlfiddle
But if temp_energydata contains new records and you need to insert it to energydata preferably with one statement then you should definitely go with the answer that Bacon Bits gave.
MERGE operation, but rather how to merge data from one table into another (which is just an UPDATE as shown here).UPDATE ed SET ed.kWh = ted.kWh FROM energydata ed INNER JOIN temp_energydata ted ON ted.webmeterID = ed.webmeterID 
energydata for dates other than those in temp_energydata, which might by surprising and undesired outcome.Update energydata set energydata.kWh = temp.kWh where energydata.webmeterID = (select webmeterID from temp_energydata as temp) 
energydata for dates other than those in temp_energydata, which might by surprising and undesired outcome.THE CORRECT WAY IS :
UPDATE test1 INNER JOIN test2 ON (test1.id = test2.id) SET test1.data = test2.data 
temp_energydata. Sure, you could add a INSERT INTO ... SELECT * FROM ... old LEFT JOIN new WHERE old.foo IS NULL (before or after the UPDATE) but it is two statements and if there is enough data the execution time could be long enough to cause issues unless you LOCK the table and if you do that your are likely to infuriate users (not enough space here to go into all the scenarios). All that said, I PREFER the UPDATE then INSERT (or vice versa) myself but it does not answer the OP's question.
temp_energydatathat are not inenergydata?