SQL Server - 如果是新的话插入行;如果存在则更新

Given:

鉴于：

Master Table:

主表：

A  B  C
a1 b1 NULL
a3 b2 NULL

New_Row Table:

New_Row表：

A  B  C
a1 b1 c1
a2 b2 c2

Desired Output:

期望的输出：

A  B  C 
a1 b1 c1
a2 b2 c2
a3 b2 NULL

I need a query that compares each columns between the Master Table and New_Row Table, and either updates the Master Table rows if all other columns in that row is identical or inserts a new row if the other columns in that row are different.

我需要一个查询比较主表和New_Row表之间的每一列，并且如果该行中的所有其他列相同则更新主表行，或者如果该行中的其他列不同则插入新行。

In the above example, the first row from the New_Row Table matches with the first row in the Master Table with the exception of the NULL value (assume Master Table is missing this data) so c1 is populated in the desired output. The second row from the New_Row Table doesn't match with any of the rows in the Master Table (as there is no a3 b2 rows in the Master), so it gets inserted in the desired output as a new row.

在上面的示例中，New_Row表中的第一行与主表中的第一行匹配，但NULL值除外（假设主表缺少此数据），因此c1填充在所需的输出中。 New_Row表中的第二行与主表中的任何行都不匹配（因为Master中没有a3 b2行），因此它作为新行插入到所需的输出中。

How would I write this query?

我该怎么写这个查询？

2 个解决方案

#1

So without a unique key on either of your tables, this is a little dicier, but still doable. You can do this either with a MERGE statement, or an explicit transaction. Which one you use is largely up to you. Personally I'm not a huge fan of MERGE because I find the syntax clunky and it can have some weird behaviors, but that's up to you. Also, i'm not sure what if any unique constraints exist on the table(s) but if there are none, and duplicates are possible, you may want to avoid MERGE as it doesn't play nicely with updating non-unique data sets.

因此，如果没有任何一个表上的唯一键，这是一个有点干扰，但仍然可行。您可以使用MERGE语句或显式事务执行此操作。你使用哪一个很大程度上取决于你。就个人而言，我不是MERGE的忠实粉丝，因为我发现语法笨拙而且可能有一些奇怪的行为，但这取决于你。此外，我不确定如果表中存在任何唯一约束，但是如果没有，并且可能重复，则可能需要避免使用MERGE，因为它不能很好地更新非唯一数据集。

With either approach, you also need to be careful how you're handling NULL values. If you just do a straight comparison (e.g. t.c = s.c) and one of the columns is null, that will never evaulate as true (since a null never equals anything; even another null). If that's something you care about, you'd need to either replace the nulls with a placeholder value (say, isnull(t.c, '') = isnull(s.c, '') or ad an additional check for whether the column(s) are null (e.g. (t.c is null or s.c is null or t.c = s.c) Setup Data

无论使用哪种方法，您还需要小心处理NULL值。如果你只是进行直接比较（例如t.c = s.c）并且其中一列是null，那么它将永远不会被视为真（因为null永远不等于任何东西;甚至是另一个null）。如果这是你关心的事情，你需要用占位符值替换空值（例如，isnull（tc，''）= isnull（sc，''）或者另外检查列是否为列为null（例如（tc为null或sc为null或tc = sc）设置数据

if object_id('tempdb.dbo.#New') is not null drop table #Master
create table #Master
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
)

if object_id('tempdb.dbo.#new') is not null drop table #new
create table #new
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
)

insert into #master
values ('a1', 'b1', null), ('a2', 'b2', null)

insert into #new
values ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2')

Method 1: MERGE

方法1：MERGE

    merge into #master t -- target
using #new s --source
    on t.a = s.a
        and t.b = s.b
        and t.c = s.c
when not matched by target then insert
    (
        a,
        b,
        c
    )
    values
    (
        s.a,
        s.b,
        s.c
    )
when matched then update
    set c = s.c;

Method 2: Explicit Transaction

方法2：显式事务

begin tran

    update t
    set c = s.c
    from #master t -- target
    inner join #new s -- source
        on t.a = s.a
            and t.b = s.b
            and t.c = s.c

    insert into #master
    (
        a,
        b,
        c
    )
    select
        s.a,
        s.b,
        s.c
    from #new s -- source
    left outer join #master t --target
        on s.a = t.a
            and s.b = t.b
            and s.c = t.c
    where t.a is null

commit tran

#2

SQL Server supports the MERGE statement to perform INSERT, UPDATE or DELETE actions in a single query.

SQL Server支持MERGE语句在单个查询中执行INSERT，UPDATE或DELETE操作。

You can find details on this statement here: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

您可以在此处找到有关此声明的详细信息：https：//docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

MERGE [master_table] AS TARGET
USING [new_row_table] AS SOURCE
ON (TARGET.A = SOURCE.A) AND (TARGET.B = SOURCE.B)
WHEN MATCHED THEN
    UPDATE SET TARGET.C = SOURCE.C
WHEN NOT MATCHED BY TARGET THEN
    INSERT (A, B, C)
    VALUES (SOURCE.A, SOURCE.B, SOURCE.C);

One caveat - MERGE is only supported in SQL Server 2008 and above.

一个警告 - MERGE仅在SQL Server 2008及更高版本中受支持。

#1

if object_id('tempdb.dbo.#New') is not null drop table #Master
create table #Master
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
)

if object_id('tempdb.dbo.#new') is not null drop table #new
create table #new
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
)

insert into #master
values ('a1', 'b1', null), ('a2', 'b2', null)

insert into #new
values ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2')