The simple solution would be:
DECLARE @MaxId int;
SELECT @MaxId = MAX(ID) FROM Production.dbo.UNITS;
UPDATE
STG.dbo.[STG UNITS]
SET
id = IsNull(@MaxId, 0) + id
;
If your staging table has gaps in the IDs, and you want to get rid of the gaps:
DECLARE @MaxId int;
SELECT @MaxId = MAX(ID) FROM Production.dbo.UNITS;
WITH cte As
(
SELECT
id,
ROW_NUMBER() OVER (ORDER BY id) As RN
FROM
STG.dbo.[STG UNITS]
)
UPDATE
S
SET
id = IsNull(@MaxId, 0) + R.RN
FROM
STG.dbo.[STG UNITS] As S
INNER JOIN cte As R ON R.id = S.id
;