declare @a table (id int, dt datetime, nme varchar(10))
insert	@a
select	1, '10/08/2006', 'nme 1c' union all
select	2, '02/03/2006', 'nme 2a' union all
select	3, '05/30/2001', 'nme 3a' union all
select	4, '01/06/1997', 'nme 4a'

declare @b table (id int, dt datetime, nme varchar(10))
insert	@b
select	1, '01/12/1989', 'nme 1a' union all
select	1, '12/09/1992', 'nme 1b' union all
select	1, '06/07/2001', 'nme 1c' union all
select	2, '02/03/2006', 'nme 2a'

id dt            nme
--------------------------------
1  '01/12/1989'  'nme 1a'  -- old
1  '12/09/1992'  'nme 1b'  -- old
1  '06/07/2001'  'nme 1c'  -- old
1  '10/08/2006'  'nme 1c'  -- new from a
2  '02/03/2006'  'nme 2a'  -- old (one from a not inserted)
3  '05/30/2001'  'nme 3a'  -- new from a
4  '01/06/1997'  'nme 4a'  -- new from a

declare @a table (id int, dt datetime, nme varchar(10))
insert	@a
select	1, '10/08/2006', 'nme 1c' union all
select	2, '02/03/2006', 'nme 2a' union all
select	3, '05/30/2001', 'nme 3a' union all
select	4, '01/06/1997', 'nme 4a'

declare @b table (id int, dt datetime, nme varchar(10))
insert	@b
select	1, '01/12/1989', 'nme 1a' union all
select	1, '12/09/1992', 'nme 1b' union all
select	1, '06/07/2001', 'nme 1c' union all
select	2, '02/03/2006', 'nme 2a'

-- to verify for correctness of data
select a.*
from @a a LEFT JOIN @b b
on a.id = b.id and a.dt = b.dt and a.nme = b.nme
where
	b.id is null and b.dt is null and b.nme is null

-- to do actual insert
Insert into @b(id, dt, nme)
select a.id, a.dt, a.nme
from @a a LEFT JOIN @b b
on a.id = b.id and a.dt = b.dt and a.nme = b.nme
where
	b.id is null and b.dt is null and b.nme is null

quote:

---

Originally posted by harsh_athalye

Using LEFT JOIN.

---