SQL Server stored procedure: UPDATE with lowest value, else INSERT if there's already a value -

migrating 20k rows or @ once, hopefully. been away sql on 10 years , struggling.

i may going entirely wrong. need update table1.value2 lowest value table2.value1, unless there's value2. if latter, need insert row value table1.value1.

table1.value1 lowest value of each id row. value2 needs next lowest.

current table1:

id1, 123, [empty] id4, 111, [empty] 

current table2:

id1, 224 id1, 331 id4, 210 id4, 551 

table1 - desired state:

id1, 123, 224 id1, 331, [empty] id4, 111, 210 id4, 551, [empty] 

table2 - desired state:

[empty] 

here's tried , update section works correctly. insert never works. think coded myself corner.

create procedure dbo.broken     --declare variables     declare @id int,             @value1 int,             @value2 int,             @tmpvalue int      --declare counter     declare @counter int     set @counter = 1      --declare cursor query     declare ctable1 cursor read_only         select id,value1, value2         table1       open ctable1      --fetch variables     fetch next ctable1 @id, @value1, @value2      --loop      while @@fetch_status = 0     begin         if (@value2 = '' or @value2 null)         begin             --update table1.value2 next lowest table1.value2 > table1.value1             set @tmpvalue = (select min(value1) table2  value1 > @value1 , id = @id)              update table1              set value2 = @tmpvalue                  id = @id              --delete affected row table2             delete table2              value1 = @tmpvalue , id = @id;         end         else         begin             --insert row in table1 id, value1 table2              set @id = (select min(id) table2)             set @tmpvalue = (select min(value1) table2 id = @id)              insert table1(id, value1, value2)              values (@id, @tmpvalue, '')              delete table2              id = @id         end          --fetch next variables         fetch next ctable1 @id, @value1, @value2     end      --close cursor table     close ctable1     deallocate ctable1 

there 20 thousand rows, i'm hoping stored procedure accomplish all. it's one-off, i'm not super concerned overhead.

the basic query needed update pretty clear:

update table1 set value2 = (     select min(t2.value1)      table2 t2     t2.value1 > table1.value1        , t2.id = table1.id     ) 

i perform insert this:

insert table1 (id, value1, value2) select id,     value1,     [empty] table2 t2 left join table1 t1     on t2.id = t1.id     , t2.value1 = t1.value1 left join table1 t1a     on t2.id = t1a.id     , t2.value1 = t1a.value2 t1.id null     , t1a.id null  delete table2 

note meets stated requirements, may not meet actual requirements - comment @jeroenmostert covers number of issues.