title: Copy data from SQL Server table to same table
body: <p>I have a database called <code>Customer</code> which has a table called <code>CustDetails</code>. The data in the table is similar to below (I have removed some of it for brevity).  <code>ID</code> is the PK.</p>
  
  <p><strong>CustDetails</strong>:</p>
  
  <pre><code>ID  CustNo  Year  StatusId
  --------------------------
  1   1231    2015    1
  2   1232    2015    2
  3   1233    2015    2
  4   1234    2014    1
  5   1235    2014    2
  </code></pre>
  
  <p>I have another database called <code>Claim</code> on the same server, which has a table called <code>ClaimDetails</code> (don't ask me why the tables aren't in the same DB but the design decision was made before I came on board and isn't something I can change.)</p>
  
  <p>The data is similar to below (again some of it removed for brevity)</p>
  
  <p><strong>ClaimDetails</strong>:</p>
  
  <pre><code>ID  ClaimNumber CustNo
  ----------------------
  1   1           1231
  2   2           1232
  3   3           1236
  4   4           1237
  </code></pre>
  
  <p>I have a few things to accomplish:</p>
  
  <ol>
  <li>Copy all rows from <code>CustDetails</code> where year is 2015 and change the year to 2016</li>
  <li>If status in the result from part 1 is 2 set it to 1 for 2016</li>
  <li>Create a negative list from to find <code>CustNo</code>'s which are in the <code>ClaimDetails</code> table but are not in the <code>CustDetails</code> table - for these create a new row in the <code>CustDetails</code> table and set the status to 3 and the year to 2016.</li>
  </ol>
  
  <p>So for the data I have listed above the end result I want to achieve is:</p>
  
  <p><strong>CustDetails</strong>:</p>
  
  <pre><code>    ID  CustNo  Year  StatusId
      --------------------------
      1   1231    2015    1
      2   1232    2015    2
      3   1233    2015    2
      4   1234    2014    1
      5   1235    2014    2
      6   1231    2016    1
      7   1232    2016    1
      8   1233    2016    1
      9   1236    2016    3
      10  1237    2016    3
  </code></pre>
  
  <p>So far I have this query:</p>
  
  <pre><code>insert into CustDetails (CustNo, Year, StatusId)
      select 
          CustNo, 2016, StatusId
      from 
          CustDetails
      where 
          Year = 2015
  </code></pre>
  
  <p>This covers part 1. I don't have the logic in place for part 2 in the query above and I guess I could just do: </p>
  
  <pre><code>UPDATE CustDetails 
  SET StatusId = 1  
  WHERE Year = 2016 AND StatusId = 2
  </code></pre>
  
  <p>Again I don't have the query done for part 3 but I am thinking I could get the <code>CustNo</code>'s that are in <code>ClaimDetails</code> but not in <code>CustDetails</code> by doing something like:</p>
  
  <pre><code>SELECT CustNo 
  FROM ClaimDetails
  WHERE CustNo NOT IN (SELECT CustNo FROM CustDetails)
  </code></pre>
  
  <p>And then do a create on the IDs for each CustNo individually.</p>
  
  <p>Question is this the best way to do this or should I do something like copy to a temp table first. And will the query for the 3rd part work ok even though they are two separate DB's - I guess if they are on the same server as long as I fully qualify the DB's it should work ok.</p>