If Record Exists, Update Else Insert

I think MERGE is what you want.

MERGEINTO    table2 t2USING   table1 t1ON      t2.email = t1.emailWHEN MATCHED THENUPDATESET     t2.col1 = t1.col1,        t2.col2 = t1.col2WHEN NOT MATCHED THENINSERT  (col1, col2)VALUES  (t1.col1, t1.col2)

Microsoft released a tool to compare data between SQL tables, this might a good option in certain situations.

Edit: Forgot to mention, it also generates a script to insert/update missing or different rows.

For completeness, I hacked up this query which does what you want, it updates existing table2 records, and adds those that are missing, based off the email address.

The 'updating' and 'insert missing' queries below are the ones you want.

BEGIN TRANcreate table #table1 (id int, fname varchar(20), email varchar(20))insert into #table1 values (1, 'name_1_updated', 'email_1')insert into #table1 values (3, 'name_3_updated', 'email_3')insert into #table1 values (100, 'name_100', 'email_100')create table #table2 (id int, fname varchar(20), email varchar(20))insert into #table2 values (1, 'name_1', 'email_1')insert into #table2 values (2, 'name_2', 'email_2')insert into #table2 values (3, 'name_3', 'email_3')insert into #table2 values (4, 'name_4', 'email_4')print 'before update'select * from #table2print 'updating'update #table2set #table2.fname = t1.fnamefrom #table1 t1where t1.email = #table2.emailprint 'insert missing'insert into #table2select * from #table1where #table1.email not in (select email from #table2 where email = #table1.email)print 'after update'select * from #table2drop table #table1drop table #table2ROLLBACK