Common writing examples for MySQL and Oracle batch insert SQL

For example:
General writing:
Summarize

For example:

Now we need to add User objects to the USER table in the database in batches

public class User{
    //Nameprivate String name;
    //Ageprivate Integer age;
    //Genderprivate Integer sex
}

Most people are familiar with MySQL and may think that batch-added SQL is written in this way, but this is not the case. This writing method is fine in MySQL, but in Oracle, it will cause an error.

MySQL writing:

INSERT INTO USER 
    (NAME,AGE,SEX)
VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');

Oracle writing:

//Multiple single inserts INSERT INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3');

//Batch insert INSERT ALL
   INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3')
SELECT 1 FROM DUAL;

It can be found that both Oracle writing methods are relatively troublesome, and batch insertion does not reduce the number of inserted column names at all. In addition, another troublesome thing is that in enterprise development, a software system may need to support multiple databases. Therefore, this newly added operation needs to adapt to two databases and maintain two sets of SQL, which greatly increases the development cost.

So is there a universal way of writing it? The answer is yes.

General writing:

INSERT INTO USER (NAME,AGE,SEX)
    select ('val1_1', 'val1_2', 'val1_3') from dual union all
    select ('val2_1', 'val2_2', 'val2_3') from dual union all
    select ('val3_1', 'val3_2', 'val3_3') from dual

This way, you can simplify the process and maintain one less set of SQL, achieving the best of both worlds.

Below is the code for various writing methods in XML files.

<!--MySQL batch insert-->
<insert id="batchInsertUser" databaseId="mysql">
   INSERT INTO USER 
            (NAME,AGE,SEX)
   VALUES
    <foreach collection="userList" index="index" item="user" separator=",">
            (#{user.name},#{user.age},#{user.sex})
  </foreach>
</insert>

<!--Oracle batch insert-->
<insert id="batchInsertUser" databaseId="oracle">
  BEGIN
  <foreach collection="userList" index="index" item="user" separator=";">
   INSERT INTO USER 
            (NAME,AGE,SEX)
   VALUES
            (#{user.name},#{user.age},#{user.sex})
  </foreach>
  ;END;
</insert>

Carefully observe the writing methods of MySQL and Oracle, because MySQL supports the above-mentioned direct insertion of multiple data after VALUES. The foreach tag only needs to loop through the contents after VALUES (); Oracle does not support this writing method and therefore needs to loop through the entire INSERT statement.

<!--General batch insert-->
<insert id="batchInsertUser" databaseId="mysql">
   INSERT INTO USER 
            (NAME,AGE,SEX)
  <foreach collection="userList" index="index" item="user" separator="union all">
     SELECT
            (#{user.name},#{user.age},#{user.sex})
     FROM DUAL
  </foreach>
</insert>

Summarize

This is the end of this article about the general writing method of MySQL and Oracle batch insert SQL. For more relevant MySQL and Oracle batch insert SQL content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: