在不存在的情况下从2个表中插入数据

Question

我想将数据从表临时插入到原始表。

结构相同，但有3个键，如下所示

table original 
{NIP_SPV  nvarchar (10),
NIP_SUB nvarchar (10),
TransActionDate date,
...
}

我想在不存在数据的地方插入数据。

我的代码如下所示

IF NOT EXISTS ( 
   SELECT * FROM table_original a Inner Join table_temp b  
   on a.transactiondate = b.transactiondate and a.nip_spv = b.nip_spv 
   and a.nip_sub = b.nip_sub )  
      Begin 
        INSERT INTO T_EmployeeGroup 
        select nip_spv,nip_sub,spv_usertype,sub_usertype,appr_year  
        from table_temp
      END
 Else 
    Begin
      Update  A
      A.column_n =B.column_n
      from table_original A
      Inner JOIN table_temp B 
      on  a.transactiondate = b.transactiondate and a.nip_spv = b.nip_spv 
      and a.nip_sub = b.nip_sub
    end

情况是

when i insert data for the second time is failed
 first case i insert 10 data
 second case i insert 20 data(10 old data from first case)

从新数据中插入10个数据失败。

我错过了什么？

Answer 1

可以为此使用OUTER JOIN并对空值进行筛选：

INSERT INTO table_original 
SELECT b.* FROM table_temp b
RIGHT JOIN table_original a ON a.transactiondate = b.transactiondate
                          AND a.nip_spv = b.nip_spv 
                          AND a.nip_sub = b.nip_sub
WHERE b.transactiondate IS NULL
  AND b.nip_spv IS NULL
  AND b.nip_sub IS NULL