How to convert INSERT ALL statement from Oracle to PostgreSQL

In Oracle you may use the conditional insert clauses, for example, an INSERT ALL statement to add multiple rows with a single command. You can insert rows into one table or even multiple tables using just one SQL statement in Oracle. However, PostgreSQL doesn’t support such statements that attempt to inserting multiple rows.

So, when you try to convert the source Oracle code that includes the INSERT ALL statement to PostgreSQL in the AWS Schema Conversion Tool, it will generate the following action item: «9996 — Severity critical — Transformer error occurred».

Let’s discover how you can address this issue.

Source Code Example

Consider the following Oracle code example that includes an INSERT ALL statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_MULTIPLEREC
IS
BEGIN
INSERT ALL
INTO tbl_CompanyEmployees VALUES (13,
'Bruce', 'Austin',
TO_DATE('02/13/1985', 'MM/DD/YYYY'))
INTO tbl_CompanyEmployees VALUES (14,
'Kevin', 'Urman',
TO_DATE('07/22/1995', 'MM/DD/YYYY'))
INTO tbl_CompanyEmployees VALUES (15,
'Steven', 'Marlow',
TO_DATE('01/15/1974', 'MM/DD/YYYY'))
SELECT * FROM DUAL;
END;

When you try to convert this code in AWS Schema Conversion Tool, you will get an error.

Possible Remedies

In order to resolve this issue, you should replace the INSERT ALL statement with a number of regular insert statements.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_MULTIPLEREC_MOD1
IS
BEGIN
INSERT INTO tbl_CompanyEmployees
VALUES (13,
'Bruce', 'Austin',
TO_DATE('02/13/1985', 'MM/DD/YYYY'));
INSERT INTO tbl_CompanyEmployees
VALUES (14,
'Kevin', 'Urman',
TO_DATE('07/22/1995', 'MM/DD/YYYY'));
INSERT INTO tbl_CompanyEmployees
VALUES (15,
'Steven', 'Marlow',
TO_DATE('01/15/1974', 'MM/DD/YYYY'));
END;

However, this solution may not work if you need to insert a lot of data. So, consider the following automated workaround that leverages the UNION ALL statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_MULTIPLEREC_MOD2
IS
BEGIN
INSERT INTO tbl_CompanyEmployees
SELECT 13,
'Bruce', 'Austin',
TO_DATE('02/13/1985', 'MM/DD/YYYY')
FROM DUAL
UNION ALL
SELECT 14,
'Kevin', 'Urman',
TO_DATE('07/22/1995', 'MM/DD/YYYY')
FROM DUAL
UNION ALL
SELECT 15,
'Steven', 'Marlow',
TO_DATE('01/15/1974', 'MM/DD/YYYY')
FROM DUAL;
END;

SCT converts this code without any errors. Now, let’s consider another source code example.

Another Example

Now we will consider Oracle’s INSERT ALL statement that inserts data in multiple tables.

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_MULTITAB
IS
BEGIN
INSERT ALL
INTO tbl_NameData VALUES (ID, FirstName, LastName)
INTO tbl_BirthDateData VALUES (ID, BirthDate)
SELECT *
FROM tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY')
ORDER BY FirstName;
END;

In this case, you should change your source Oracle code as follows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_MULTITAB_MOD1
IS
BEGIN
INSERT INTO tbl_NameData (ID,
FirstName,
LastName)
SELECT ID,
FirstName,
LastName
FROM tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY')
ORDER BY FirstName;
INSERT INTO tbl_BirthDateData (ID,
BirthDate)
SELECT ID,
BirthDate
FROM tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY')
ORDER BY FirstName;
END;

SCT converts this code without any issues!

Converting INSERT ALL statement with WHEN clause

Please, check one more example that includes a condition in the executed INSERT ALL statement. The source Orcale code will look as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_CONDTAB
IS
BEGIN
INSERT ALL
WHEN BirthDate <= TO_DATE('01/01/1985', 'MM/DD/YYYY') THEN
INTO tbl_CompanyEmployees_1
WHEN BirthDate > TO_DATE('01/01/1985', 'MM/DD/YYYY') THEN
INTO tbl_CompanyEmployees_2
SELECT *
FROM tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY');
END;

In this case the SCT will use the correct conversion approach, separating the original statement in two parts. The problem is that SCT doesn’t produce an error message, but the generated code is not logically correct.

SCT ignores the WHEN condition and uses the exactly the same date intervals as in the original statement for both options of the original condition. So, you will need to manually separate the contition and create a pair of INSERT-SELECT statements for each option. If the original code looks as follows, SCT converts it correctly to PostgreSQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE PROCEDURE PRC_INSERTALL_CONDTAB_MOD1
IS
BEGIN
INSERT INTO tbl_CompanyEmployees_1
SELECT *
FROM tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('01/01/1985', 'MM/DD/YYYY');

INSERT INTO tbl_CompanyEmployees_2
SELECT *
FROM tbl_CompanyEmployees
WHERE BirthDate > TO_DATE('01/01/1985', 'MM/DD/YYYY')
AND BirthDate <= TO_DATE('12/01/1989', 'MM/DD/YYYY');
END;

Do you have other questions or concerns about AWS SCT or Oracle to PostgreSQL migration issues? Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your database migration projects.

Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.

Source of technical information: Andrii Oseledko, Developer at DB Best.

Blog: Oracle to PostgreSQL migration with AWS SCT: How to convert INSERT ALL statement

Source Code Example

Possible Remedies

Another Example

Converting INSERT ALL statement with WHEN clause

Related posts

Getting started