oracle一张表向另一张表插入不重复数据merge into用法

时间:2021-03-15 00:41:12


参考1:

由于项目中需要用到批量插入,为了防止用户插入重复的数据,需要先判断插入的数据是不是已经存在,如果存在则忽略这次插入,否则插入这条数据,刚开始的时候是先用一条SQL语句判断,如果返回结果是真(记录已经存在),则忽略,否则插入这条数据,这样的话插入一条数据就要发起两个到数据库的连接,后来发现这样做效率实在太低了,Google了一下,发现oracle数据库支持merge语句,做了个测试,成功了,记录下来供本人及同行以后查阅之用。

数据库:TEST

12345 CREATE TABLE TEST(    ID      NUMBER NOT NULL,    NAME    VARCHAR2(30)NOT NULL,    SEX     VARCHAR2(2) DEFAULT '男')
插入两条数据:

12 INSERT INTO TESTVALUES(1,'SUNZHENXING','男')INSERT INTO TESTVALUES(2,'SUNHAILONG','女')
MERGE语句:

123456 MERGEINTO TEST A USING TEST B ON (A.NAME=B.NAME)WHEN MATCHEDTHENUPDATE SET A.SEX='女'WHERE A.NAME='SUNZHENXING'WHEN NOT MATCHEDTHENINSERT VALUES (3,'SUNZHENXING','女')

参考2:

  1. 可省略的update 或者insert
  2.    MERGE INTO products p   
  3.     2 USING newproducts np   
  4.     3 ON (p.product_id = np.product_id)   
  5.     4 WHEN MATCHED THEN  
  6.     5 UPDATE  
  7.     6 SET p.product_name = np.product_name,   
  8.     7 p.category = np.category;

  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.

2,当条件不满足的时候把newproducts表中的数据INSERT 到表products中。

  1. MERGE INTO products p   
  2.     USING newproducts np   
  3.      ON (p.product_id = np.product_id)   
  4.      WHEN NOT MATCHED THEN  
  5.      INSERT  
  6.      VALUES (np.product_id, np.product_name,   
  7.      np.category);   

3,带条件的insert 和update

  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.    ON (p.product_id = np.product_id)   
  4.    WHEN MATCHED THEN  
  5.    UPDATE  
  6.    SET p.product_name = np.product_name   
  7.    WHERE p.category = np.category;

 insert 和update 都带有where 字句

 
  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.     ON (p.product_id = np.product_id)   
  4.     WHEN MATCHED THEN  
  5.     UPDATE  
  6.      SET p.product_name = np.product_name,   
  7.      p.category = np.category   
  8.     WHERE p.category = 'DVD'  
  9.     WHEN NOT MATCHED THEN  
  10.      INSERT  
  11.      VALUES (np.product_id, np.product_name, np.category)   
  12.     WHERE np.category != 'BOOKS'  

4,无条件的insert

  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.   ON (1=0)   
  4.   WHEN NOT MATCHED THEN  
  5.    INSERT  
  6.    VALUES (np.product_id, np.product_name, np.category)   
  7.    WHERE np.category = 'BOOKS'  

ps:

1、My table has four cols:ID,MyDataTime,TagName,Value.Excep ID,other three cols should be added"" when be quoted.

2、My code:

MERGE INTO ZTT."AllDataTable221_0" A --目标表
USING ZTT."AllDataTable221" B --源表
ON (A."MyDateTime" = B."MyDateTime") --条件--原来用mydatetime作为条件,发现不能二次插入,必须用主键ID作为判断条件
WHEN NOT MATCHED THEN --not matched 与insert一起用,match与update一起用
INSERT (A.ID,A."MyDateTime",A."TagName",A."Value") VALUES(B.ID,B."MyDateTime",B."TagName",B."Value") WHERE B."TagName" = '30MAD12CY122|XQ01';
merge into中on的判断条件,应该已主键判断,因为只有主键才有唯一性,用其他条件判断可能会出错
-- Oracle数据库 
-- SQL说明 : 将source_tb中的数据merge到target_tb表中 
-- source_tb中存在同target_tb主键相同的记录时,更新target_tb表 
-- source_tb不存在同target_tb主键相同的记录时,插入target_tb表 
-- SQL正文 : 
merge into target_tb t using source_tb s 
on (t.pk = s.pk) --执行条件 
when matched then --如果数据存在,更新数据 
update set t.name = s.name,t.address = s.address 
when not matched then --如果数据不存在,插入数据 
insert (t.name,t.address) values (s.name,s.address)