MogDB Supports Insert All

Availability

This feature is available since MogDB 5.0.0.

Introduction

Oracle 9i version of the new Insert All/First syntax, expanding the original Insert syntax, so that the Insert statement from the original can only be inserted into a table to expand to insert multiple tables at the same time, but also according to the judgment conditions to determine the data inserted into which table, so that the previous multiple Insert statement can be merged into one statement to avoid repeated scanning of data. Insert All and Insert First statements are basically the same syntax, the only difference is: Insert first for each line of data, only insert into the first table when the condition is established, do not continue to check other conditions, Insert All: for each line of data, check each when condition, if the condition is met, insert. If the conditions are met, insert all. This feature is compatible with Oracle's Insert All/First function.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

In daily production life, we often encounter the need to insert a batch of data into different tables according to different conditions, such as the personal information collected nationwide, according to the origin of the individual inserted into the corresponding provincial table. For ordinary Insert statement, each province needs to query and scan all the data to filter the information of their own province and insert it into the corresponding table of their own province, which requires scanning the whole amount of data for many times.

MogDB is compatible with Oracle's Insert All/First function, the Insert statement from the original can only be inserted into a table to expand to insert multiple tables at the same time, but also according to the judgment conditions to determine the data inserted into which table, so that the previous multiple Insert statements can be combined into one statement to avoid repeated scanning of data. The execution results are consistent with Oracle, no core during execution, no exception error report inconsistent with Oracle, the only difference is that MogDB does not support error_log_clause syntax.

Constraints

• The corresponding error_log_clause syntax in Oracle is not supported.

Syntax Description

Oracle's Insert syntax is defined as follows:

MogDB's syntax rules are basically the same as Oracle's above, the only difference is that MogDB does not support error_log_clause.

In addition, MogDB supports Explain Analyze to see the specific number of rows inserted per table, as shown below:

Example

-- Create table and insert test data
CREATE TABLE tt02 (a INT, b INT, a1 INT, b1 INT);
INSERT INTO tt02
VALUES (generate_series(1, 10), generate_series(11, 20), generate_series(21, 30), generate_series(31, 40));
CREATE TABLE tt02_1 (a INT);
CREATE TABLE tt02_2 (a INT, b INT);
CREATE TABLE tt02_3 (a1 INT, b1 INT);
CREATE TABLE tt02_4 (a INT, b INT, a1 INT, b1 INT);

-- use case Insert all
INSERT ALL 
        WHEN a1 < 25 THEN 
                INTO tt02_1(a) VALUES(a)
                INTO tt02_2(a, b) VALUES(a, b)
        WHEN a1 < 30 THEN
                INTO tt02_3(a1, b1) VALUES(a, a1)
                INTO tt02_4(a, b1) VALUES(b, a1)
        ELSE
                INTO tt02_4(a, b) VALUES(a, b)
                INTO tt02_4(a, b) VALUES(a, b)
SELECT a, b, a1 FROM tt02;

-- result
MogDB =# SELECT * FROM tt02_1 ORDER BY a; a
---
 1
 2
 3
 4
(4 rows)

MogDB=# SELECT * FROM tt02_2 ORDER BY a, b;
 a | b
---+----
 1 | 11
 2 | 12
 3 | 13
 4 | 14
(4 rows)

MogDB=# SELECT * FROM tt02_3 ORDER BY a1, b1;
 a1 | b1
----+----
  1 | 21
  2 | 22
  3 | 23
  4 | 24
  5 | 25
  6 | 26
  7 | 27
  8 | 28
  9 | 29
(9 rows)

MogDB=# SELECT * FROM tt02_4 ORDER BY a, b, a1, b1;
 a  | b  | a1 | b1
----+----+----+----
 10 | 20 |    |
 10 | 20 |    |
 11 |    |    | 21
 12 |    |    | 22
 13 |    |    | 23
 14 |    |    | 24
 15 |    |    | 25
 16 |    |    | 26
 17 |    |    | 27
 18 |    |    | 28
 19 |    |    | 29
(11 rows)

-- use case Insert First
TRUNCATE TABLE tt02_1;
TRUNCATE TABLE tt02_2;
TRUNCATE TABLE tt02_3;
TRUNCATE TABLE tt02_4;
INSERT first 
        WHEN a1 < 25 THEN
                INTO tt02_1(a) VALUES (a)
                INTO tt02_2(a, b) VALUES (a, b)
        WHEN a1 < 30 THEN
                INTO tt02_3(a1, b1) VALUES (a, a1)
                INTO tt02_4(a, b1) VALUES (b, a1) 
        ELSE
                INTO tt02_4(a, b) VALUES (a, b)
                INTO tt02_4(a, b) VALUES (a, b)
SELECT a, b, a1 FROM tt02;

-- result
MogDB=# SELECT * FROM tt02_1 ORDER BY a;
 a
---
 1
 2
 3
 4
(4 rows)

MogDB=# SELECT * FROM tt02_2 ORDER BY a, b;
 a | b
---+----
 1 | 11
 2 | 12
 3 | 13
 4 | 14
(4 rows)

MogDB=# SELECT * FROM tt02_3 ORDER BY a1, b1;
 a1 | b1
----+----
  5 | 25
  6 | 26
  7 | 27
  8 | 28
  9 | 29
(5 rows)

MogDB=# SELECT * FROM tt02_4 ORDER BY a, b, a1, b1;
 a  | b  | a1 | b1
----+----+----+----
 10 | 20 |    |
 10 | 20 |    |
 15 |    |    | 25
 16 |    |    | 26
 17 |    |    | 27
 18 |    |    | 28
 19 |    |    | 29
(7 rows)