Insert new Row of Real table based upon a loop executed on a temp table

Question

Obviously this code below is wrong, but you'll have to forgive that as I have not really done loops in SQL before.

While (Select oldSysNr, DoW.DoWC, DoW.WorkStart, DoW.WorkEnd, DoW.ContractHours From #TempTable 
Join AppData.TSCHEDDOW as DoW 
On DoW.ShiftSystemNumber = oldSysNr)


    Insert into AppData.TSCHEDDOW 
    (
        #TempTable.newSysNr,
        DoWC,
        WorkStart,
        WorkEnd,
        ContractHours
    )
    Select
    #TempTable.newSysNr,
    DoWC,
    WorkStart,
    WorkEnd,
    ContractHours
    From AppData.TSCHED 
End;

The intended result is that this loops through the tempTable's oldSysNr and when that and the AppData.TSCHEDDOW SysNr match, then it would insert new rows into AppData.TSCHED with only newSysNr being the attribute that changes, while keeping the old rows.

Here are the desired results:

AppData.TSCHEDDOW
    SysNr   DoWC   WorkStart   WorkEnd  ContractHours
    --------------------------------------------------
    24      1        12:00      8:00         8
    25      2        12:00      8:00         8
    26      2        9:00       6:00         8
    27      5        7:00       3:00         8
    54      1        12:00      8:00         8
    55      2        12:00      8:00         8
    56      2        9:00       6:00         8
    57      5        7:00       3:00         8

My question now is what am I doing wrong here with this loop, and what should I be doing instead? Any help that I can get and resources to look at for this question will be greatly appreciated.

Here is the Sample Data:

#TempTable
oldSysNr     newSysNr
------------------------
24            54
25            55
26            56
27            57

AppData.TSCHEDDOW
        SysNr   DoWC   WorkStart   WorkEnd  ContractHours
        --------------------------------------------------

        24      1        12:00      8:00         8
        25      2        12:00      8:00         8
        26      2        9:00       6:00         8
        27      5        7:00       3:00         8

The information in which newSysNr is populated from comes from the same table as oldSysNr, this is the result of a creating a temp table that has self joined based upon a year parameter, one being the year that is copied and the year that all this information will get copied to. If anything else is needed to fully answer this question, please let me know.

Answer 1

Provided understand what you are actually describing, I think all you need is a simple UPDATE statement:

CREATE TABLE TSCHEDDOW (SysNr int,
                        DoWC int,
                        WorkStart time,
                        WorkEnd time,
                        ContractHours int)
INSERT INTO TSCHEDDOW
VALUES(24,1,'12:00','8:00',8),
      (25,2,'12:00','8:00',8),
      (26,2,'9:00','6:00',8),
      (27,5,'7:00','3:00',8)

CREATE TABLE #temp (oldSysNr int,newSysNr int)
INSERT INTO #temp
VALUES(24,54),
      (25,55),
      (26,56),
      (27,57);

UPDATE TS
SET SysNr = T.newSysNr
FROM TSCHEDDOW TS
     JOIN #temp T ON TS.SysNr = T.oldSysNr

DB<>Fiddle

Edit: Here are my "extra" guesses, but otherwise, they need to explain more and show better expected results:

INSERT INTO TSCHEDDOW (SysNr,DoWC, WorkStart, WorkEnd, ContractHours)
SELECT T.newSysNr,
       TS.DoWC,
       TS.WorkStart,
       TS.WorkEnd,
       TS.ContractHours
FROM TSCHEDDOW TS
     JOIN #temp T ON TS.SysNr = T.oldSysNr;

DB<>Fiddle

ALTER TABLE TSCHEDDOW ADD NewSysNr int;

....

UPDATE TS
SET newSysNr = T.newSysNr
FROM TSCHEDDOW TS
     JOIN #temp T ON TS.SysNr = T.oldSysNr;

DB<>Fiddle