If Record Exists, Update Else Insert

Question

Hi there,

I'm trying to move some data between two SQL Server 2008 tables. If the record exists in Table2 with the email from Table1 then update that record with the data from Table1, else insert a new record.

In Table1 I have a number of columns; firstname, surname, email and so on.

I'm not quite sure how to structure the query to update Table2 if the email from Table1 exists or insert a new row if email from Table1 does not exist in Table2.

Tried doing a few searches on google but most solutions seem to work on creating some stored procedure. So I wondered if anyone might know how to build a suitable query that might do the trick?

Thank you.

Answer 1

I think MERGE is what you want.

Answer 2

The simple answer is that you can't do what you want - if you're after a straight query then its going to be exclusively one of:

• SELECT
• INSERT
• UPDATE
• DELETE

You can't combine two of the above in a query - therefore to get round the issue you're going to have to use logic somewhere (either in the client or in the server) to determine if the data exists and hence to decide whether you need to INSERT or UPDATE. If you want to do this at the server then it will have to be a stored procedure.

Answer 3

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 TRAN

create 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 #table2

print 'updating'
update #table2
set #table2.fname = t1.fname
from #table1 t1
where t1.email = #table2.email

print 'insert missing'
insert into #table2
select * from #table1
where #table1.email not in (select email from #table2 where email = #table1.email)

print 'after update'
select * from #table2

drop table #table1
drop table #table2

ROLLBACK

Answer 4

MERGE
INTO    table2 t2
USING   table1 t1
ON      t2.email = t1.email
WHEN MATCHED THEN
UPDATE
SET     t2.col1 = t1.col1,
        t2.col2 = t1.col2
WHEN NOT MATCHED THEN
INSERT  (col1, col2)
VALUES  (t1.col1, t1.col2)