New post
Question
· Mar 17, 2022

How to perform multiple row SQL insert to a Linked Oracle Database record using DLLImport

#SQL #InterSystems IRIS

Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.


We have tried using statements that are accepted within Oracle but not when using Intersystems DLL along the lines of :

INSERT  INTO mytable (column1, column2, column_n)
  SELECT expr1, expr2, expr_n FROM Dual  UNION ALL
   SELECT expr1, expr2, expr_n FROM Dual;
  
SELECT mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  SELECT * FROM Dual;

However, this produces error since Dual is not a recognised table.

We would prefer not to use multiple INSERT statements due to the overheads of running what would be a large number of records.

Is there a recommended solution to achieve this? Many thanks

Product version: IRIS 2020.1
Discussion (4)0
Log in or sign up to continue
Benjamin De Boe · Apr 8, 2022

Where Oracle uses the FROM DUAL pseudo-table, IRIS will just do without a FROM clause altogether for cases where you just want to select a single row of constant expressions. The following statement should work:

INSERT INTO mytable (column1, column2, column_n)
  SELECT expr1, expr2, expr_n UNION ALL
   SELECT expr1, expr2, expr_n;
1 0
Dom GMMH NHS Trust · Apr 13, 2022

Many thanks Benjamin,

For some relason when I initially attempted this I couldn't get to work but will retry and confirm.

We subsequently came across a bigger issue in that when trying to run a multiple insert with the ~45K records required (with intention to do as a weekly process) that this would far exceed what I understand to be a character limit for the DDLImport query size.

Am therefore going to look at using the SQL Outbound Adapter to try to achieve this and will post results back again regarding in hope might be useful for others.

0 0