SQL MERGE statement to update data

Question

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

User · Answer

THE CORRECT WAY IS    UPDATE test1 INNER JOIN test2 ON  test1 id   test2 id  SET test1 data   test2 data

User · Answer

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

User · Answer

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

User · Answer

Update energydata set energydata kWh   temp kWh  where energydata webmeterID    select webmeterID from temp energydata as temp

User · Answer

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  gt  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

User · Answer

UPDATE ed SET ed kWh   ted kWh FROM energydata ed INNER JOIN temp energydata ted ON ted webmeterID   ed webmeterID

[sql-server-2008] SQL MERGE statement to update data

Examples related to sql-server-2008

Examples related to tsql