Update and insert from one table with multiple same ID rows but different column attributes to second table

I would like to update the records from one table to another table and if there are new records then insert those records. In this case, the new records are having the same ID but different column attributes. When I am running the update and insert query, it’s only considering the last entry from table one. It seems that it’s overwriting the data in table 2. How to approach this? I would like to add 2 additional records in table 2 even though the Premise_ID is the same for them.

1st table

SELECT Premise_ID, Division, InstallationType
FROM [GIS_NewJersey].[sde].[SAP_Load]
WHERE Premise_ID = '8000004592'

Result:

2nd Table

SELECT Premise_ID, Division, InstallationType
FROM [GIS_NewJersey].[sde].[PREMISE]
WHERE Premise_ID = '8000004592'

Result:

Answer

You can use a MERGE statement

WITH Source AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.SAP_Load
    WHERE Premise_ID = '8000004592'
),
Target AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.PRtsqlEMISE
    WHERE Premise_ID = '8000004592'
)
MERGE Target t
USING Source s
  ON t.Premise_ID = s.Premise_ID
WHEN MATCHED THEN
  UPDATE SET
    Division = s.Division,
    InstallationType = s.InstallationType
WHEN NOT MATCHED THEN
  INSERT (Premise_ID, Division, InstallationType)
    VALUES (s.Premise_ID, s.Division, s.InstallationType)
;

Remove the two WHERE filters in order to match all Premise_ID rows