title: Synchronizing two tables using a stored procedure, only updating and adding rows where values do not match
body: <h2><strong>The scenario</strong></h2>
  
  <p>I've got two tables with identical structure. </p>
  
  <pre class="lang-TSQL prettyprint-override"><code>TABLE [INFORMATION], [SYNC_INFORMATION]
  
      [ITEM] [nvarchar](255) NOT NULL
      [DESCRIPTION] [nvarchar](255) NULL
      [EXTRA] [nvarchar](255) NULL
      [UNIT] [nvarchar](2) NULL
      [COST] [float] NULL
      [STOCK] [nvarchar](1) NULL
      [CURRENCY] [nvarchar](255) NULL
      [LASTUPDATE] [nvarchar](50) NULL
      [IN] [nvarchar](4) NULL
      [CLIENT] [nvarchar](255) NULL
  </code></pre>
  
  <p>I'm trying to create a synchronize procedure that will be triggered by a scheduled event at a given time every day.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_SynchronizeInformation]
  AS
  BEGIN
      SET NOCOUNT ON;
  
      --Update all rows
      UPDATE TARGET_TABLE
      SET TARGET_TABLE.[DESCRIPTION] = SOURCE_TABLE.[DESCRIPTION],
          TARGET_TABLE.[EXTRA] = SOURCE_TABLE.[EXTRA],
          TARGET_TABLE.[UNIT] = SOURCE_TABLE.[UNIT],
          TARGET_TABLE.[COST] = SOURCE_TABLE.[COST],
          TARGET_TABLE.[STOCK] = SOURCE_TABLE.[STOCK],
          TARGET_TABLE.[CURRENCY] = SOURCE_TABLE.[CURRENCY],
          TARGET_TABLE.[LASTUPDATE] = SOURCE_TABLE.[LASTUPDATE],
          TARGET_TABLE.[IN] = SOURCE_TABLE.[IN],
          TARGET_TABLE.[CLIENT] = SOURCE_TABLE.[CLIENT]
      FROM SYNC_INFORMATION TARGET_TABLE 
          JOIN LSERVER.dbo.INFORMATION SOURCE_TABLE ON TARGET_TABLE.ITEMNO = SOURCE_TABLE.ITEMNO
      WHERE TARGET_TABLE.ITEMNO = SOURCE_TABLE.ITEMNO
  
      --Add new rows
      INSERT INTO SYNC_INFORMATION (ITEMNO, DESCRIPTION, EXTRA, UNIT, STANDARDCOST, STOCKTYPE, CURRENCY_ID, LASTSTANDARDUPDATE, IN_ID, CLIENTCODE)
      SELECT 
          src.ITEM, 
          src.DESCRIPTION,
          src.EXTRA,
          src.UNIT,
          src.COST,
          src.STOCKTYPE,
          src.CURRENCY_ID,
          src.LASTUPDATE,
          src.IN,
          src.CLIENT
      FROM LSERVER.dbo.INFORMATION src
          LEFT JOIN SYNC_INFORMATION targ ON src.ITEMNO = targ.ITEMNO
      WHERE
          targ.ITEMNO IS NULL
  END
  </code></pre>
  
  <p>Currently, this procedure (including some others that are also executed at the same time) takes about 15 seconds to execute.
  I'm planning on adding a "Synchronize" button in my work interface so that users can manually synchronize when, for instance, a new item is added and needs to be used the same day.</p>
  
  <p>But in order for me to do that, I need to trim those 15 seconds as much as possible.</p>
  
  <hr>
  
  <p>Instead of updating every single row, like in my procedure, is it possible to only update rows that have values that does not match?<br>
  This would greatly increase the execution speed, since it doesn't have to update all the 4000 rows when maybe only 20 actually needs it.</p>
  
  <p>Can this be done in a better way, or optimized?<br>
  Does it need improvements, if yes, where?</p>
  
  <p>How would you solve this?</p>
  
  <p>Would also appreciate some time differences between the solutions so I can compare them.</p>
  
  <h2>UPDATE</h2>
  
  <p>Using marc_s's <code>CHECKSUM</code> is really brilliant. The problem is that in some instances the information creates the same checksum. Here's an example, due to the classified content, I can only show you 2 columns, but I can say that all columns have identical information except these 2. To clarify: this screenshot is of all the rows that had duplicate <code>CHECKSUM</code>s. These are also the only rows with a hyphen in the <code>ITEM</code> column, I've looked.</p>
  
  <p><img src="https://i.stack.imgur.com/XSFQR.png" alt="enter image description here"></p>
  
  <p>The query was simply</p>
  
  <pre><code>SELECT *, CHECKSUM(*) FROM SYNC_INFORMATION
  </code></pre>