EXCEPT operator query to differentiate between new and changed rows
You could use the technique from this post.

--Demo setup
set nocount on
drop table if exists #T1
drop table if exists #T2
Create table #T1 (Id int, OtherData varchar(10))
Create table #T2 (Id int, OtherData varchar(10))

insert into #T1(Id,OtherData) values(1,'NewRow')
insert into #T1(Id,OtherData) values(2,'ChgRowB')

insert into #T2(Id,OtherData) values(2,'ChgRowA')

-----------------------------------------------
--The actual query
-----------------------------------------------

-- NEW
SELECT A.*, 'NEW'  AS 'CHANGE_TYPE'
FROM #T1  A
LEFT JOIN #T2  B ON B.ID = A.ID
WHERE B.ID IS NULL
UNION
-- MODIFIED
SELECT B.*, 'MODIFIED'  AS 'CHANGE_TYPE'
FROM (
        SELECT * FROM #T1 
        EXCEPT
        SELECT * FROM #T2
    ) S1
INNER JOIN #T2  B ON S1.ID = B.ID;




| Id | OtherData | CHANGE_TYPE |
|----|-----------|-------------|
| 1  | NewRow    | NEW         |
| 2  | ChgRowA   | MODIFIED    |