如果sql server 2005中的原始表不存在，则从一个表更新到另一个表

Question

我有 2 个具有相同列的表 - T1、T2。我想根据键列从 T2 中存在的列更新表 T1 列：如果键列存在，则从 T2 更新 T1 的其余列，如果不存在，则插入从 T2 到 T1 的整行。

这个查询不起作用：

IF EXISTS (SELECT keyC FROM T2 WHERE keyC in (select keyC from T1)) 
UPDATE T1 SET T1.c1 = T2.c1,
T1.c2 = T2.c2,
from  T2 WHERE  T2.keyC in (select keyC from T1)
ELSE (INSERT INTO T1 select * from T2)

知道如何修复它吗？

提前致谢，

格雷格

Answer 1

由于您使用 SQL Server 2005，因此无法使用合并并且必须执行两条语句。一项更新，一项插入。

declare @T1 table (keyC int, c1 int)
declare @T2 table (keyC int, c1 int)

insert into @T1 values (1, 1)

insert into @T2 values (1, 10)
insert into @T2 values (2, 20)

-- Update all rows
update @T1 set
  c1 = T2.c1
from @T1 as T1
  inner join @T2 as T2
    on T1.keyC = T2.keyC

-- Insert new rows
insert into @T1 (keyC, c1)
select keyC, c1
from @T2 as T2
where not exists (select *
                  from @T1 as T1
                  where T1.keyC = T2.keyC)

Answer 2

在SQL2008中你可以使用优雅的MERGE语句：

MERGE T1 AS target
USING T2 AS source ON (target.KeyC = source.KeyC)
WHEN MATCHED THEN 
    UPDATE SET  c1 = source.c1,
                c2 = source.c2
WHEN NOT MATCHED THEN   
    INSERT (c1, c2)
    VALUES (source.c1, source.c2);

Answer 3

对于 SQL Server 2008，请查看如何使用 MERGE 语句。

MERGE T1 AS target
USING T2 AS source
ON (target.keyC = source.keyC)
WHEN MATCHED THEN 
    UPDATE SET c1 = source.c1, c2 = source.c2
WHEN NOT MATCHED THEN   
    INSERT (keyC, c1, c2)
    VALUES (source.keyC, source.c1, source.c2)

编辑：OP将问题从2008年更改为2005年

对于早期版本，您需要2次操作：

UPDATE target
    SET c1 = source.c1, c2 = source.c2
    FROM T1 AS target
        INNER JOIN T2 as source
            ON target.keyC = source.keyC

INSERT INTO T1
    (keyC, c1, c2)
    SELECT keyC, c1, c2
        FROM T2
        WHERE NOT EXISTS(SELECT NULL FROM T1 WHERE keyC = T2.keyC)