SQL Moving Row to Identical Table WITHOUT auto-increment (SQL Server 2008)
<p>I have two tables - "RENTED" and "HISTORY." Once an item is returned I need to move it to the "HISTORY" table using a procedure. The tables are identical in every way. The primary key is just a number, but is NOT auto-incremented. When I try to move a row from Rented to History, I get a clash because the primary keys both have the number 2 for an ID number. I know I just need to find the max value of the primary key in the HISTORY table, then add the row after. Seemed easy, ended up being hard to do. Lastly, I delete the row from the RENTED Table, which I am able to do. Please assist me with the row movement.  Thanks! </p>

<p>Also, I looked at some other similar code samples/answers here, but didn't find a solution quite yet. </p>

<pre><code>Create Procedure spMoveToHistory
@RENTED_OUT_NUM bigint

AS

Begin

  Insert Into HISTORY
  Select *
  From RENTED_OUT
  Where RENTED_OUT_NUM = @RENTED_OUT_NUM
    Select @RENTED_OUT_NUM = (MAX(HISTORY_NUM)+1)
    From HISTORY

Delete From RENTED
Where RENTED_OUT_NUM = @RENTED_OUT_NUM


End
</code></pre>

<p>So in this procedure, I just want to enter the number 2 and take the 2nd record in the RENTED table and move over to the HISTORY table's next available row. See below for better visualization of the tables (a few columns omitted)</p>

<pre><code>**RENTED TABLE:** 
RENTED_OUT_ID (PK) | ITEM_NAME | ITEM_DESC | DATE_RENTED | DATE_RETURNED
1                      data         data       data           data
2   move this          data         data       data           data         
3                      data         data       data           data         


**HISTORY TABLE:**
HISTORY_NUM   (PK) | ITEM_NAME | ITEM_DESC | DATE_RENTED | DATE_RETURNED
1                      data         data       data           data         
2                      data         data       data           data            
-&gt;   INSERT HERE
</code></pre>