Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming you want an actual <a href="http://technet.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow noreferrer">SQL Server <code>MERGE</code></a> statement:</p> <pre><code>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); </code></pre> <p>If you also want to delete records in the target that aren't in the source:</p> <pre><code>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; </code></pre> <hr> <p>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.</p> <p>First, there are several blogs which report <a href="http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx" rel="nofollow noreferrer">concurrency issues with the <code>MERGE</code> statement</a> 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 <code>HOLDLOCK</code> or <code>SERIALIZABLE</code> lock hint:</p> <pre><code>MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target [...] </code></pre> <p>You can also accomplish the same thing with more restrictive transaction isolation levels.</p> <p>There are <a href="http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/" rel="nofollow noreferrer">several other known issues</a> with <code>MERGE</code>. (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.</p> <p>As it is, even though I've never had any problems with the <code>MERGE</code> statement myself, I always use the <code>WITH (HOLDLOCK)</code> hint now, and I prefer to use the statement only in the most straightforward of cases.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL MERGE statement to update data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBacon Bits
    1. USBacon Bits
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL MERGE statement to update data
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe `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.
      singulars
      1. PO
      1. USAndriy M
    2. CO@AndriyM Which is why I said "If you also want to delete records in the target that aren't in the source". I'm not sure how this would be confusing?
      singulars
      1. PO
      1. USBacon Bits
    3. COWell, perhaps not confusing but, to an inexperienced person, it *might* not be entirely obvious that, when they want to use the temp set to update a subset of rows (in particular, a subset of members) in the main table, the rows deleted will also include those members that weren't supposed to be updated. I'm not insisting (that it may not be obvious), though, as I might as well just be overcautious there, so please disregard my comment if you think so.
      singulars
      1. PO
      1. USAndriy M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload