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!
Also, I looked at some other similar code samples/answers here, but didn't find a solution quite yet.
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
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)
**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
-> INSERT HERE
You can use the OUTPUT INTO clause to insert the deleted record into the history table in one go. The syntax will be this:
declare @max_id bigint
select @max_id = max(HISTORY_NUM)+1 from history
DELETE FROM rented
OUTPUT @max_id
, DELETED.ITEM_NAME
, DELETED.ITEM_DESC
, DELETED.DATE_RENTED
, DELETED.DATE_RETURNED
INTO history
WHERE RENTED_OUT_NUM = @RENTED_OUT_NUM