如果记录存在，则更新其他插入 [英] If Record Exists, Update Else Insert

问题描述

我正在尝试在两个SQL Server 2008表之间移动某些数据.如果该记录存​​在于表2中，并且带有来自表1的电子邮件，则使用表1中的数据更新该记录，否则插入新记录.

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.

在表1中，我有许多列；名，姓，电子邮件等.

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

如果表1中的电子邮件存在，我不确定如何构造查询以更新表2；如果表2中不存在来自表1的电子邮件，我不知道如何插入新行.

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.

我尝试在Google上进行一些搜索，但是大多数解决方案似乎都可以通过创建一些存储过程来工作.因此，我想知道是否有人会知道如何构建合适的查询来解决问题?

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

推荐答案

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)

这篇关于如果记录存在，则更新其他插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！