title: SQL Insert distinct records
body: <p>I'm trying to batch some insertion scripts (avoiding duplicates) and I've come across some tables that have no primary key (I know...I didn't create them and I cannot modify them). Basically what I've done is grabbed the rows I need, put them into a temporary table (<code>[TempTable]</code>), and updated some values in them.</p>
  
  <p>Now I need to re-insert <code>DISTINCT TOP</code> values from <code>[TempTable]</code> into <code>[OriginalTable]</code> in batches. To do this, I imagine I would need a column in the temp table (which I've created...let's call it <code>[ValuesInserted]</code>), that specifies which columns were just inserted.</p>
  
  <p>I would do an <code>INSERT</code> statement to put <code>DISTINCT</code> values into the original table, using <code>TOP</code> to batch it.</p>
  
  <pre><code>INSERT INTO [OriginalTable]
  SELECT DISTINCT TOP (1000) *
  FROM [TempTable]
  </code></pre>
  
  <p>Then I would UPDATE the temp table to have ValuesInserted set to 1 for the records that were just inserted. <strong>This is where I'm stuck:</strong></p>
  
  <pre><code>UPDATE /*TOP (1000) - Doesn't work*/ [TempTable]
  SET [ValuesInserted] = 1
  ???
  </code></pre>
  
  <p>Then I would DELETE those records from the temp table so that my next <code>INSERT</code> statement (using <code>TOP</code>) will not capture the previous set of records.</p>
  
  <pre><code>DELETE
  FROM [TempTable]
  WHERE [ValuesInserted] = 1
  </code></pre>
  
  <p>The main problem I'm having is that just running an <code>UPDATE</code> on just the <code>TOP (1000)</code> rows, doesn't capture all of the records that may have duplicates in <code>[TempTable]</code>. I also cannot perform an <code>INNER JOIN</code>  on all columns on two copies of <code>[TempTable]</code> because this is being run on many different tables using dynamic SQL. Basically, the script needs to be generic (not specific to any table), but it should be assumed that there is no primary key.</p>
  
  <p>The following generic sample captures the idea:</p>
  
  <pre><code>Val1    Val2    ValuesInserted
  1       1       0
  1       2       0
  1       3       0
  1       4       0
  1       5       0
  1       6       0
  1       7       0
  1       8       0
  1       9       0
  1       1       0             &lt;--Duplicate
  2       1       0
  2       2       0
  2       3       0
  2       4       0
  2       5       0
  2       6       0
  2       7       0
  2       8       0
  2       9       0
  2       1       0             &lt;--Duplicate
  3       1       0
  3       2       0
  3       3       0
  3       4       0
  3       5       0
  3       6       0
  3       7       0
  3       8       0
  3       9       0
  3       1       0             &lt;--Duplicate
  1       2       0             &lt;--Duplicate
  1       3       0             &lt;--Duplicate
  </code></pre>
  
  <p>Doing an <code>UPDATE TOP (5)</code> on this above data set will only update the first 5 records:</p>
  
  <pre><code>Val1    Val2    ValuesInserted
  1       1       1             &lt;--Updated
  1       2       1             &lt;--Updated
  1       3       1             &lt;--Updated
  1       4       1             &lt;--Updated
  1       5       1             &lt;--Updated
  1       6       0
  1       7       0
  1       8       0
  1       9       0
  1       1       0             &lt;--Duplicate
  2       1       0
  2       2       0
  2       3       0
  2       4       0
  2       5       0
  2       6       0
  2       7       0
  2       8       0
  2       9       0
  2       1       0             &lt;--Duplicate
  3       1       0
  3       2       0
  3       3       0
  3       4       0
  3       5       0
  3       6       0
  3       7       0
  3       8       0
  3       9       0
  3       1       0             &lt;--Duplicate
  1       2       0             &lt;--Duplicate
  1       3       0             &lt;--Duplicate
  </code></pre>
  
  <p>I need to update any records that match the top 5 records like so:</p>
  
  <pre><code>Val1    Val2    ValuesInserted
  1       1       1             &lt;--Updated
  1       2       1             &lt;--Updated
  1       3       1             &lt;--Updated
  1       4       1             &lt;--Updated
  1       5       1             &lt;--Updated
  1       6       0
  1       7       0
  1       8       0
  1       9       0
  1       1       1             &lt;--Updated
  2       1       0
  2       2       0
  2       3       0
  2       4       0
  2       5       0
  2       6       0
  2       7       0
  2       8       0
  2       9       0
  2       1       0             &lt;--Duplicate
  3       1       0
  3       2       0
  3       3       0
  3       4       0
  3       5       0
  3       6       0
  3       7       0
  3       8       0
  3       9       0
  3       1       0             &lt;--Duplicate
  1       2       1             &lt;--Updated
  1       3       1             &lt;--Updated
  </code></pre>
  
  <p>If you can make your idea work on this sample, I can apply it to my specific case.</p>
  
  <p>Am I approaching this completely wrong, or am I missing something? I'm looking for a solution that <strong>doesn't hog resources</strong> because the script is batched and is running on very large databases on high-impact servers.</p>
  
  <p>The closest topic I could find on this was:
  <a href="https://stackoverflow.com/questions/7163271/using-distinct-in-sql-update">Using Distinct in SQL Update</a>.
  However, the answers given would not work when using <code>TOP</code>.</p>
  
  <p>EDIT: This apparently wasn't clear at the beginning. The first thing I'm doing is grabbing rows from <code>[OriginalTable]</code> and putting them into <code>[TempTable]</code>. These rows are initially unique. However, I perform an update that modifies some of the values, yielding data like the sample above. <strong>From there</strong>, I need to grab <code>DISTINCT</code> rows and re-insert them into <code>[OriginalTable]</code>.</p>