topic Re: Modify Statement in Native SQL in Application Development and Automation Discussions

Modify Statement in Native SQL

Former Member — Tue, 27 Jul 2010 15:23:06 GMT

Hi,

1. IS there Modify statement in Native statement, same as we have in Open Sql?

If Yes, then pls provide the sample code.

If no, then what will happen if I insert the same data (with same key fields) into database. Also pls provide a sample code.

2. How to delete all the data of a particular table using Native Sql.

Regards,

Pankaj Bist.

Re: Modify Statement in Native SQL

Former Member — Tue, 27 Jul 2010 16:08:42 GMT

Hi Pankaj,

the answers can be significantly different, depending on what database SAP runs on.

1. The ABAP sentence MODIFY allows you to insert new records or, if they already exist, updates the existing ones.

In Native SQL for Oracle, in older versions, you cannot insert or update (just like MODIFY does) in a single statement. You must e.g. INSERT, and if this fails (because of the primary key constraint) then you must perform an UPDATE.

In newer releases of Oracle (for example 9, 10g), however, there's a new statement MERGE, that works quite similarly as MODIFY in ABAP. More details in [Oracle SQL reference 10g release 2|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm].

2. In Native SQL for Oracle, there's a simple statement to do so:


TRUNCATE TABLE your_table.

But watch out! you cannot rollback this statement.

I hope this helps. Kind regards,

Alvaro

Re: Modify Statement in Native SQL

Former Member — Wed, 28 Jul 2010 15:08:16 GMT

Hi,

I have to insert multiple records from an internal table into d/b table using Native SQL query.

Do i need to write Native SQL query in LOOP end Loop. or there is some other way (like the use of PERFORMING in case of Native SELECT query). It whould be helpful if you can provide some sample code.

Regards,

Pankaj.

Re: Modify Statement in Native SQL

former_member182114 — Thu, 29 Jul 2010 02:06:32 GMT

Hi Pankaj,

To pass from an ABAP internal table to native sql you will need a LOOP ENDLOOP.

Follow a code sample Native SQL - Source Code

Regards, Fernando Da Ró

Re: Modify Statement in Native SQL

Former Member — Tue, 09 Aug 2011 06:25:05 GMT

Not sure about your case, but I would prefer to use native SQL only if you do not need to do modification on the data or other complex logic.

This means doing an Insert using a Select query without going through an internal table.

Re: Modify Statement in Native SQL

former_member187732 — Mon, 25 Feb 2013 18:32:17 GMT

Hello Fernando,

I am trying to insert multiple records from internal table to database table using native sql using loop..endloop. Can you provide the source code native sql?

Thanks,

James

Re: Modify Statement in Native SQL

Former Member — Thu, 04 Jul 2013 12:31:39 GMT

Hi Fernando,

General way inserting data using Native SQL is

EXEC SQL.

INSERT INTO <TABLENAME> (<FIELD1>, <FIELD2>, <FIELD3>)

VALUES (<VALUE1>,<VALUE3><VALUE3>)

ENDEXEC.

Since in your case there is a internal table we require to loop at the table into work area, and the content of the work area is passed to the value.

LOOP it_tab1 into wa_itab1.

INSERT INTO <TABLENAME> (<FIELD1>, <FIELD2>,....<FIELDN>)

VALUES (:wa_itab1-field1,:wa_itab1-field2,.....:wa_itab1-fieldN)

ENDEXEC.

ENDLOOP.