Multi-valued inserts for postgresql

36 views
Skip to first unread message

Naren

unread,
Nov 9, 2014, 11:07:06 PM11/9/14
to scrip...@googlegroups.com
I am using scriptella to read data from Oracle and load it into Postgresql. The performance for the insert seems slow in postgres inspite of using statement.batchSize=1000. I found that multi-valued insert statements can speedup the performance.  

An example of a multi-valued insert is below: 

1
2
3
4
INSERT INTO table (column1, column2, …)
VALUES
    (value1, value2, …),
    (value1, value2, …) ,...;

How can I implement multi-valued insert in an ETL file in scriptella? I tried using global variables as below but it doesn't work : 

<query connection-id="con141">select * from sales  
   <script connection-id="jexl">
        etl.globals['globalVar'] =  etl.globals['globalVar'] + " VALUES (?1,?2,?3,?4,?5,?6,?7)";
    </script>  
   <script connection-id="conws" if="rownum%100==0">INSERT INTO t_comp_100_141 ?{etl.globals['globalVar']};</script>

Thanks

Fyodor Kupolov

unread,
Nov 12, 2014, 1:53:04 AM11/12/14
to scrip...@googlegroups.com
Hi Naren,

I'd rather try exporting Oracle data into CSV file and try COPY command to import it. It can be done in chunks e.g. 10000 records per file.

Naren

unread,
Nov 13, 2014, 11:51:01 AM11/13/14
to scrip...@googlegroups.com
Thanks Fyodor. I will try that. 
Reply all
Reply to author
Forward
0 new messages