如何使用MERGE SQL语句插入新值 [英] How to use MERGE SQL-Statement to insert new values

问题描述

我有2张桌子:

A:
----------
Key | Value | PK_A


B:
-----------
Key | Value

如何仅从表B中插入表B中的表A中的新(键，值).我尝试使用MERGE，但不确定如何与新的主键PK_A一起使用

How to insert new (key, values) in table A from table B which are in table B only. I've tried to use MERGE but I'm not sure how to use with new primary key PK_A

MERGE INTO A
USING (SELECT key, value FROM B) 
ON (A.Key = B.key AND A.value = B.value)
WHEN NOT MATCHED THEN INSERT (D.key, D.value, ???) VALUES (B.key, B.value, ???);

推荐答案

创建一个序列以填充PK_A.

Create a sequence to populate PK_A.

CREATE SEQUENCE PK_A_SEQ;

然后

MERGE INTO A
USING (SELECT key, value FROM B) 
ON (A.Key = B.key AND A.value = B.value)
WHEN NOT MATCHED THEN INSERT (D.key, D.value, D.PK_A) VALUES (B.key, B.value, PK_A_SEQ.NEXTVAL);

这篇关于如何使用MERGE SQL语句插入新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！