title: How to insert a non sequential Id from a table in an insert into clause
body: <p>I need to insert an ID of table 1 in table2. The problem is the fact that the sequential rows in table 1 do not have a sequential Id. (someone have erased some rows, hence no sequential Ids for the sequential rows)</p>
  
  <p>Doing a <code>select * from table 1</code> results in:</p>
  
  <p>Table 1</p>
  
  <pre><code>Id    Value1
  ------------
  2       X
  4       Y
  10      Z
  12      XYZ
  14      ZD
  121     XD
  122     ZS
  ------------
  </code></pre>
  
  <p>The desired result of table 2 is:</p>
  
  <pre><code>   Id    Value1   Value2      Date
      -----------------------------------
      1       67       2       2018-05-15
      2       67       4       2018-05-15
      3       67      10       2018-05-15
      4       67      12       2018-05-15
      5       67      14       2018-05-15
      6       67      121      2018-05-15
      7       67      122      2018-05-15
      ------------------------------------
  </code></pre>
  
  <p>The code to insert values in table 2. I only need table 1 to copy the right Id. </p>
  
  <pre><code>declare @value int
  set @value=(select count(*) from table1) 
  while @value&gt;0
  
              begin
                  insert into (table2)
                  values ('67',@HOW_TO_RETRIEVE THE CORRECT ID from TABLE 1?,getdate())
                  set @value=@value-1
  
              end
  </code></pre>
  
  <p>of course with @value it will not work, I have tried to use the select clause inside values but to no avail. Thanks. </p>