[ORACLE] 다중INSERT

INSERT INTO TABLE_NAME(COL1, COL2, COL3, COL4)
SELECT *
  FROM (
  SELECT 1, 2, 3, 4 FROM DUAL UNION ALL
  SELECT 5, 6, 7, 8 FROM DUAL UNION ALL
  SELECT 9, 8, 7, 6 FROM DUAL
  );
  
  
 INSERT ALL
   INTO TABLE_NAME(COL1, COL2, COL3, COL4) VALUES (1, 2, 3, 4)
   INTO TABLE_NAME(COL1, COL2, COL3, COL4) VALUES (5, 6, 7, 8)
   INTO TABLE_NAME(COL1, COL2, COL3, COL4) VALUES (9, 8, 7, 6)
 SELECT * FROM DUAL;
 
 
 INSERT INTO TABLE_NAME(A, B, C, D) VALUES (1, 2, 3, 4);
 INSERT INTO TABLE_NAME(A, B, C, D) VALUES (5, 6, 7, 8);
 INSERT INTO TABLE_NAME(A, B, C, D) VALUES (9, 8, 7, 6);

xml에서 활용.하면 아래와 같다.

 

<insert id="insertTest" parameterType="HashMap">
    INSERT INTO TEST
     (   A
       , B
       , C
       , D
      ) SELECT *
      FROM (
      <foreach collection="Content" item="item" separator="UNION ALL">
      SELECT  #{item.A}
            , #{item.B}
            , #{item.C}
            , #{item.D}
        FROM DUAL
      </foreach>
      )
</insert>
    

<insert id="insertTest" parameterType="HashMap">
    INSERT ALL		
      <foreach collection="Content" item="item">
      INTO TEST
     (   A
       , B
       , C
       , D
      ) VALUES
      (   #{item.A}
        , #{item.B}
        , #{item.C}
        , #{item.D}
      )
      </foreach>
    SELECT * FROM DUAL
</insert>