question

i have two tables called t and t1 . Problem : if no data is available in #t1 then i need to insert and if data is avialable then i need to update . suppose if i got blank first time in terminationdate column of #t1 from #t then i need to insert and if i get data with same active date with same id then i need to update date in termination date column of #t1 and i should not have 2 nd entry in table . how do i handle it. CREATE TABLE #t ( id INT ,activedate VARCHAR(15) ,terminationdate VARCHAR(20) ) CREATE TABLE #t1 ( id INT ,activedate VARCHAR(15) ,terminationdate VARCHAR(20) ) INSERT INTO #t VALUES ( 1 ,'2006-01-01' ,NULL ) , ( 1 ,'2006-01-01' ,'2006-01-31' ) SELECT * FROM #t SELECT * FROM #t1 MERGE INTO #t1 ds USING ( SELECT DISTINCT * FROM #t ) al ON ds.id = al.id AND ds.activedate = al.activedate AND ds.terminationdate = al.terminationdate WHEN NOT MATCHED THEN INSERT ( id ,activedate ,terminationdate ) VALUES ( id ,activedate ,terminationdate ) WHEN MATCHED AND ds.terminationdate = '' THEN DELETE WHEN MATCHED AND ds.id = al.id AND ds.activedate = al.activedate THEN UPDATE SET ds.terminationdate = al.terminationdate; Expected out put : First time > id activedate terminationdate 1 2006-01-01 null IF i got terminationdate(assume 2006- 8-30) forsame id WITH same active DATE then record should UPDATE instead OF 2 nd record entry > id activedate terminationdate 1 2006-01 - 01 2006 - 8 - 30 Note : IF we can achieve by altering the above query it will be great otherwise share the alternative methods TO achieve it.