DECLARE @DMLTable TABLE(id CHAR(3), name VARCHAR(5), dob DATe, address VARCHAR(10))
insert into @DMLTable
SELECT '001', 'abc1', '20101201', 'addr1' union all
SELECT '002', 'abc2', '20111212', 'addr2' union all
SELECT '003', 'abc2', '20121201', 'addr3'
DECLARE @STATUSTable TABLE(id CHAR(3), name VARCHAR(5), dob DATe, address VARCHAR(10), status VARCHAR(40))
insert into @STATUSTable (id, name , dob , address)
SELECT '001', 'abc1', '20101201', 'addr1' union all
SELECT '002', 'abc2', '20111212', 'addr11' union all
SELECT '003', 'abc2', '20121201', 'addr13' union all
SELECT '004', 'abc1', '20101201', 'addr1' union all
SELECT '005', 'abc2', '20101201', 'addr2' union all
SELECT '006', 'abc2', '20101201', 'addr3'
SELECT s.*
, CASE WHEN d.ID IS NULL THEN 'New'
WHEN s.ID = d.Id AND s.name = d.name AND s.Address = d.Address AND d.dob = s.dob THEN 'No Change'
ELSE 'Updated' END As Status
FROM @DMLTable d
RIGHT JOIN @STATUSTable s on d.id = s.id
--
Chandu