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

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 {You DMLTable here } AS d
RIGHT JOIN {Your STATUS table here} AS s on d.id = s.id

quote:

---

Originally posted by chbala85

Hi chandu ,
In my table having 87k records how i will do.

Thanks,

Krish

---

Just Blue marked names should be your table names
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