topic Re: Insert massive data using EXEC SQL in Application Development and Automation Discussions

Insert massive data using EXEC SQL

former_member295881 — Sat, 08 Oct 2011 17:22:06 GMT

Hello Expert,

I'm trying to insert data in a custom table. Before insert I'm holding all my records in an internal table. I know using EXEC SQL I can insert data like as this.

===================================================

try.

EXEC SQL

insert into TABLENAME

(

COLUMN1

COLUMN2

)

VALUES

(

:VALUE1

:VALUE2

)

ENDEXEC.

catch cx_sy_native_sql_error into gv_oref.

gv_txterror = gv_oref->get_text( ).

message gv_txterror type 'I'.

endtry.

==================================================

However my problem is I've a lot of columns in my table (45). Also I'll have to update thousands of records. I Can't do it column by column and row by row. So I've tried to update this table using the following code.

=================================================

try.

exec sql.

insert into ZCED_ORDER_IDOC from p_git_order_idoc.

endexec.

catch cx_sy_native_sql_error into exc_ref.

error_text = exc_ref->get_text( ).

message error_text type 'I'.

endtry.

endif.

=================================================

But in exception text I get error that INTO & VALUE are missing. I understand the error but as I said I can't insert value by value. So I'm wondering is there a way to insert many records at a time and If an exception occurs then I can show that message to user?

Many thanks in advance.

Re: Insert massive data using EXEC SQL

Former Member — Sat, 08 Oct 2011 18:29:57 GMT

I think VALUE is obligatory, you can omit the column (if you need to fill all fields), but you can use a variable:

LOOP AT ITAB.
   OPEN SQL.
        INSERT INTO <TABLE>
            VALUE( :ITAB-FIELD1, :ITAB-FIELD2,....., :ITAB-FIELDN)
   ENDEXEC.
ENDLOOP.

Max

Re: Insert massive data using EXEC SQL

former_member295881 — Sat, 08 Oct 2011 19:14:12 GMT

Man thanks Max for your input but I can't afford to use LOOP ENDLOOP because expecting to INSERT millions of record. I've solve my issue by using TRY ENDTRY block and if there is a duplicate entry then I display error. Here is my code.

try.

insert zced_order_idoc_records from table p_git_order_idoc_records.

catch cx_sy_open_sql_db into sql_ref.

err_text = sql_ref->get_text( ).

message err_text type 'I'.

endtry.

Thanks

Re: Insert massive data using EXEC SQL

former_member295881 — Sat, 08 Oct 2011 19:15:59 GMT

Solved it myself

Re: Insert massive data using EXEC SQL

Former Member — Wed, 12 Oct 2011 18:58:44 GMT

Can you share how you solved it please?