ADB for mysql 【INSERT [IGNORE] INTO table_name】迁移 DWS 改写方法

INSERT [IGNORE] INTO table_name [( column_name [, …] )] [VALUES] [(value_list[, …])] [query];

参数

• IGNORE：可选参数，若系统中已有相同主键的记录，新记录不会被写入。

• column_name：可选参数，列名。

• query：通过定义查询，将一行或多行数据插入表中。

DWS改写方法

CREATE TABLE PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
INSERT INTO PRODUCTS VALUES (1600, 'PLAY GYM', 'TOYS');
INSERT INTO PRODUCTS VALUES (1601, '**ZE', 'TOYS');
INSERT INTO PRODUCTS VALUES (1602, 'HARRY POTTER', 'DVD');

CREATE TABLE NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);

INSERT INTO NEWPRODUCTS VALUES (1601, '**ZE', 'TOYS');
INSERT INTO NEWPRODUCTS VALUES (1603, 'HARRY POTTER', 'TOYS');
INSERT INTO NEWPRODUCTS VALUES (1604, 'WAIT INTERFACE', 'BOOKS');

--方法1  MERGE  INTO   
CREATE OR REPLACE PROCEDURE INSERT_DATA
AS
BEGIN
MERGE INTO PRODUCTS P  
USING NEWPRODUCTS NP  
ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
WHEN NOT MATCHED THEN 
  INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY);
END;
/

--调用此存储过程。
CALL INSERT_DATA();

--方法2  NOT EXISTS   
CREATE OR REPLACE PROCEDURE INSERT_DATA
AS
BEGIN
INSERT INTO PRODUCTS   
SELECT * FROM NEWPRODUCTS NP  
WHERE NOT EXISTS (SELECT 1 FROM PRODUCTS MP WHERE NP.PRODUCT_ID = MP.PRODUCT_ID);
END;
/

--调用此存储过程。
CALL INSERT_DATA();