title: Updating 500,000 rows in SQL Server with unique data
body: <p>So I'm working on some database 'de-identification' where essentially each piece of information changes. On most of the smaller tables a simple while update wasn't too time consuming (up through tables with 10,000 rows or so. I've now moved to one that has approximately 500,000 rows.  </p>
  
  <p>I've read that the fastest way to do this sort of "update" is to in fact just select into a temporary table updating the column you need. (I read it here.  <a href="https://stackoverflow.com/questions/3711217/fastest-way-to-update-120-million-records">Fastest way to update 120 Million records</a>)</p>
  
  <p>The problem with this is that the OP is updating all similar values with a single value where as each of my values are different, ie he updates the null rows in a single column to -1, I'm updating each column in my new row to more or less a random date;  This is what I've got so far.</p>
  
  <pre><code>--The only Index on Treatments is a Clustered Primary Key (TreatmentID)
  SELECT * INTO #Treatments_temp
  FROM Treatments
  CREATE CLUSTERED INDEX IDX_Treatments ON #Treatments_temp(TreatmentID)
  
  SET @rows = (SELECT TOP 1 TreatmentID
               FROM Treatments
               ORDER BY TreatmentID Desc)
  
  WHILE (@rows &gt; 0)
    BEGIN
  
      --There are only 500,000 records in this table from count(*) but the PK is much 
      --higher (some records are deleted, made in error ETC so this if statement is my
      --attempt to bypass the code for @rows that don't actually exist.
  
      IF (SELECT TreatmentID FROM #Treatments_temp WHERE TreatmentID = @rows) IS NOT NULL
        BEGIN
        DECLARE @year INT;
        DECLARE @month INT;
        DECLARE @date INT;
        DECLARE @newStartDate SMALLDATETIME;
        DECLARE @multiplier FLOAT;
  
        SET @multiplier = (SELECT RAND());
  
        SET @year = @multiplier * 99 + 1900;
        SET @month = @multiplier * 11 + 1;
        SET @date = @multiplier * 27 + 1;
  
        SET @newStartDate = DATEADD(MONTH,((@year-1900)*12)+@month-1,@date-1);
  
        UPDATE #Treatments_temp
        SET StartDate = @newStartDate
        WHERE TreatmentID = @rows
  
        UPDATE #Treatments_temp
        SET EndDate = DATEADD(MINUTE, @timebetween, @newStartDate)
        WHERE TreatmentID = @rows
        END
  
    SET @rows = @rows - 1
    END
  </code></pre>