Modify Statement in Native SQL

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[former_member182114]

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ó

[Former Member]

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.

[former_member187732]

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

[Former Member]

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.