Insert a NEW Row using a Single Value from LAST row with Stored Procedure

Please refer to the screen captures below.

I am trying to create a stored procedure in SQL Server 2014 that does the following:

1. Sets the “Complete” BIT in the LAST record (e.g. row #1062) to TRUE

2. Takes a the “DateTo” value from the LAST record (highlighted in yellow) and INSERTS it into the “DateFrom” column in a NEW row.

That should be all that is required, as the other columns (BatchID, DateTo, Complete) are automatically populated.

Any help would be greatly appreciated!

Answer

You need two statement. First update the latest row, then insert the new one:

with cte as (select top (1) complete from billing_batch order by dateto desc)
update cte set complete = 1;

insert into billing_batch (datefrom)
select max(dateto) 
from billing_batch;