title: INSERT INTO SELECT strange order using UNION
body: <p>I had a typical non-normalized table (<code>tempTable</code>) with multiple numbered columns (<code>rep1</code>,<code>rep2</code>,...). 
  So i wrote a script to insert the non-normalized data into a normalized table (<code>myTable</code>):</p>
  
  <pre><code>insert into myTable
  select idRep,rep FROM
  (
      select idRep, ISNULL(rep1,'') as rep FROM tempTable
      union
      select idRep, ISNULL(rep2,'') as rep FROM tempTable
      union
      select idRep, ISNULL(rep3,'') as rep FROM tempTable
      union
      select idRep, ISNULL(rep4,'') as rep FROM tempTable
      union
      select idRep, ISNULL(rep5,'') as rep FROM tempTable
  ) as t
  </code></pre>
  
  <p>Note: The table <code>myTable</code> also contains an auto-incremented <code>IDENTITY</code> column as its <code>PRIMARY KEY</code>.</p>
  
  <p>The order rep1, rep2, rep3, rep4, rep5 is important in my scenario. Strangely, when I executed the script, the data wasn't inserted in the correct order such as the auto-generated id '1000' had the value from 'rep3' and the id '1001' had the value from 'rep1'.</p>
  
  <p>Why is that? How was the script executed?</p>