文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> DML> INSERT

INSERT

分享
更新时间: 2019/08/09 GMT+08:00

概述

Oracle的INSERT语句用于将单条或多条记录插入到表中。

说明:

对于INSERT FIRST,如果输入的非SELECT子句包含注释,这些注释会被添加到输出的末尾。

INSERT ALL

Oracle的INSERT ALL语句可通过单个INSERT语句向单个或多个表中插入多行。目标查询将转化为公用表表达式(CTE)。

输入:INSERT ALL
INSERT ALL 
   INTO ap_cust
      VALUES ( customer_id, 
       program_id, delivered_date ) 
   INTO ap_orders  ( ord_dt, Prg_id ) 
      VALUES ( order_date, program_id ) 
SELECT program_id, delivered_date, customer_id, order_date
  FROM order
WHERE deptno = 10;

输出

WITH Sel AS
( SELECT program_id, delivered_date, customer_id, order_date
  FROM order
WHERE deptno = 10 )
, ins1 AS ( INSERT INTO ap_cust ( SELECT customer_id, 
       program_id, delivered_date FROM Sel ) RETURNING 1 )
INSERT INTO ap_orders  ( ord_dt, Prg_id ) ( SELECT order_date, program_id FROM Sel );
输入:INSERT ALL 和条件
INSERT ALL
 WHEN deptno <= 10 THEN
  INTO emp12
     VALUES 
 ( empno,ename,job,mgr,
 hiredate,sal )
   WHEN deptno > 10 and deptno <= 20 
THEN
   INTO emp13
      VALUES (empno,ename,job,
  mgr,hiredate,sal)
WHEN deptno > 20 THEN
     INTO emp14
       VALUES (empno,ename,job,
   mgr,hiredate,sal)
SELECT empno,ename,job,mgr,
hiredate,sal,Deptno
  FROM emp 
     WHERE job='MANAGER';

输出

WITH Sel AS
( SELECT empno,ename,job,mgr,
hiredate,sal,Deptno
  FROM emp 
     WHERE job='MANAGER' )
, ins1 AS ( INSERT INTO emp12 ( SELECT empno,ename,job,mgr,
 hiredate,sal FROM Sel WHERE deptno <= 10 ) RETURNING 1 )
, ins2 AS ( INSERT INTO emp13 ( SELECT empno,ename,job,
  mgr,hiredate,sal FROM Sel WHERE deptno > 10 and deptno <= 20 ) RETURNING 1 )
INSERT INTO emp14 ( SELECT empno,ename,job,
   mgr,hiredate,sal FROM Sel WHERE deptno > 20 );
输入:INSERT ALL 和ELSE
INSERT ALL
   WHEN deptno <= 10 THEN
    INTO emp12
   WHEN deptno > 10 AND deptno <= 20 THEN
    INTO emp13
   ELSE
   INTO emp14 (empno, ename, job)
         VALUES (empno, ename, job)
SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
WHERE job='MANAGER';

输出

WITH Sel AS
( SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
WHERE job='MANAGER' )
, ins1 AS ( INSERT INTO emp12 ( SELECT empno, ename, job, mgr, hiredate, sal, deptno FROM Sel WHERE deptno <= 10 ) RETURNING 1 )
, ins2 AS ( INSERT INTO emp13 ( SELECT empno, ename, job, mgr, hiredate, sal, deptno FROM Sel WHERE deptno > 10 AND deptno <= 20 ) RETURNING 1 )
INSERT INTO emp14 (empno, ename, job) ( SELECT empno, ename, job FROM Sel ) 
MINUS SELECT empno, ename, job FROM Sel WHERE (deptno <= 10) OR
(deptno > 10 AND deptno <= 20);

INSERT FIRST

Oracle的INSERT FIRST语句用于在first条件为true时执行INSERT语句,而其他语句会被忽略。目标查询将转化为公用表表达式(CTE)。

输入:INSERT FIRST
INSERT FIRST
WHEN deptno <= 10 THEN
     INTO emp12
WHEN comm > 500 THEN
     INTO emp13
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
  FROM emp
WHERE deptno IS NOT NULL;

输出

WITH Sel AS
( SELECT ROW_NUMBER() OVER() Ins_First_RN, 
empno, ename, job, mgr, hiredate, sal, comm, deptno
  FROM emp
WHERE deptno IS NOT NULL )
, ins1 AS ( INSERT INTO emp12 (SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM Sel WHERE deptno <= 10 ) RETURNING 1 ) 
INSERT INTO emp13 (SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM 
 (SELECT * FROM Sel WHERE comm > 500 ) s1 LEFT JOIN 
 (SELECT Ins_First_RN FROM Sel WHERE deptno <= 10 ) s2 
 ON s1.Ins_First_RN = s2.Ins_First_RN WHERE s2.Ins_First_RN IS NULL );
输入:INSERT FIRST和 ELSE
INSERT FIRST
WHEN ottl < 100000 THEN
      INTO small_orders
         VALUES ( oid, ottl, sid, 
         cid,cl,cem )
WHEN ottl > 100000 AND ottl < 200000 THEN
       INTO medium_orders (moid, mottl,  
       msid, mcid,mcl,mcem)
         VALUES ( oid, ottl, sid, 
         cid,cl,cem )
ELSE
     INTO special_orders
SELECT o.order_id oid, o.orders_total 
   ottl, o.customer_id cid
   , o.sales_rep_id sid,    
   c.credit_limit cl, c.cust_email cem
  FROM orders1 o, customers1 c
 WHERE o.customer_id = c.customer_id;

输出

WITH Sel AS
( SELECT ROW_NUMBER() OVER() Ins_First_RN, 
o.order_id oid, o.orders_total 
   ottl, o.customer_id cid
   , o.sales_rep_id sid,    
   c.credit_limit cl, c.cust_email cem
  FROM orders1 o, customers1 c
 WHERE o.customer_id = c.customer_id )
, ins1 AS ( INSERT INTO small_orders (SELECT oid, ottl, sid, 
         cid,cl,cem FROM Sel WHERE ottl < 100000 ) RETURNING 1 ) 
, ins2 AS ( INSERT INTO medium_orders (moid, mottl,  
       msid, mcid,mcl,mcem) (SELECT oid, ottl, sid, 
         cid,cl,cem FROM 
 (SELECT * FROM Sel WHERE ottl > 100000 AND ottl < 200000 ) s1 LEFT JOIN 
 (SELECT Ins_First_RN FROM Sel WHERE ottl < 100000 ) s2 
 ON s1.Ins_First_RN = s2.Ins_First_RN WHERE s2.Ins_First_RN IS NULL ) RETURNING 1 ) 
INSERT INTO special_orders (SELECT oid, ottl, cid, sid, cl, cem FROM Sel S1 LEFT JOIN 
 ( SELECT Ins_First_RN FROM Sel WHERE ottl < 100000 UNION ALL SELECT Ins_First_RN FROM Sel WHERE ottl > 100000 AND ottl < 200000 ) s2 
 ON s1.Ins_First_RN = s2.Ins_First_RN WHERE s2.Ins_First_RN IS NULL );

MERGE

MERGE with UPDATE and INSERT

(配置参数mergeImplementation默认设置为WITH。)

MERGE是ANSI标准的SQL语法运算符,用于从一个或多个源中选择行以更新或插入表或视图。您可以指定更新或插入目标表或视图的条件。

目前,GaussDB 200,GaussDB 300和DWS不支持此功能。Migration Tool 使用多种方法将MERGE迁移到GaussDB 200,GaussDB 300和DWS兼容的SQL中。

配置参数为mergeImplementation:

输入

MERGE INTO employees01 e
USING (SELECT empid, ename, startdate, address
FROM hr_records
WHERE empid > 100) h
ON (e.id = h.empid)
WHEN MATCHED THEN
UPDATESET e.address = h.address
, e.ename = h.ename
WHEN NOT MATCHED THEN
INSERT (empid,ename,startdate,address)
VALUES (h.empid,h.ename,h.startdate,h.address);

输出

WITH h AS ( SELECT empid, ename, startdate, address
FROM  hr_records
WHERE  empid > 100
)
, UPD_REC AS ( UPDATEemployees01 e
SET e.address = h.address
, e.ename = h.ename
 FROM h
WHERE e.id = h.empid
returning e.*
)
INSERT INTO employees01 ( empid, ename, startdate, address )
SELECT h.empid, h.ename, h.startdate, h.address
FROM h
WHERE NOT EXISTS ( SELECT 1 FROM UPD_REC e
WHERE e.id = h.empid );

MERGE with UPDATE and INSERT

(参数mergeImplementation默认设置为SPLIT。)

输入

MERGE INTO employees01 e
USING (SELECT empid, ename, startdate, address
FROM hr_records
WHERE empid > 100) h
ON (e.id = h.empid)
WHEN MATCHED THEN
UPDATESET e.address = h.address
, e.ename = h.ename
WHEN NOT MATCHED THEN
INSERT (empid,ename,startdate,address)
VALUES (h.empid,h.ename,h.startdate,h.address);

输出

UPDATE  employees01 e
SET e.address = h.address
, e.ename = h.ename
FROM ( SELECT empid, ename, startdate, address
FROM  hr_records
WHERE  empid > 100
) h
WHERE e.id = h.empid;
INSERT INTO employees01 ( empid, ename, startdate, address )
SELECT h.empid, h.ename, h.startdate, h.address
FROM ( SELECT empid, ename, startdate, address
FROM  hr_records
WHERE  empid > 100
) hLEFT OUTER JOINemployees01 e
ON e.id = h.empid
WHERE e.id IS NULL;

MERGE with only UPDATE

(配置参数mergeImplementation默认设置为WITH。)

输入

MERGE INTO student a
USING (SELECT id, sname, score
FROM student_n) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.sname = b.sname
, a.score = b.score
WHERE a.score > 600;

输出

WITH b AS ( SELECT id, sname, score
FROM student_n )
UPDATEstudent a
SET a.sname = b.sname
, a.score = b.score
FROM b
WHERE a.score > 600
AND a.id = b.id;

MERGE with only UPDATE

(配置参数mergeImplementation默认设置为SPLIT。)

输入

MERGE INTO student a
USING (SELECT id, sname, score
FROM student_n) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.sname = b.sname
, a.score = b.score
WHERE a.score > 600;

输出

UPDATEstudent a
SET a.sname = b.sname
, a.score = b.score
FROM ( SELECT id, sname, score
FROM student_n
) b
WHERE a.score > 600
AND a.id = b.id;

MERGE with only INSERT

(配置参数mergeImplementation默认设置为WITH。)

输入

MERGE INTO employees01 e
USING (SELECT empid, ename, startdate, address
FROM hr_records
WHERE empid > 100) h
ON (e.id = h.empid)
WHEN NOT MATCHED THEN
INSERT (empid,ename,startdate,address)
VALUES (h.empid,h.ename,h.startdate,h.address);

输出

WITH h AS ( SELECT empid, ename, startdate, address
FROM  hr_records
WHERE  empid > 100
)
INSERT INTO employees01 ( empid, ename, startdate, address )
SELECT h.empid, h.ename, h.startdate, h.address
FROM h
WHERE NOT EXISTS ( SELECT 1 FROM UPD_REC e
WHERE e.id = h.empid );

MERGE with only INSERT

(配置参数mergeImplementation默认设置为SPLIT。)

输入

MERGE INTO employees01 e
USING (SELECT empid, ename, startdate, address
FROM hr_records
WHERE empid > 100) h
ON (e.id = h.empid)
WHEN NOT MATCHED THEN
INSERT (empid,ename,startdate,address)
VALUES (h.empid,h.ename,h.startdate,h.address);

输出

INSERT INTO employees01 ( empid, ename, startdate, address )
SELECT h.empid, h.ename, h.startdate, h.address
FROM ( SELECT empid, ename, startdate, address
FROM  hr_records
WHERE  empid > 100
) h LEFT OUTER JOINemployees01 e
ON e.id = h.empid
WHERE e.id IS NULL;

  

分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区