DELETE & INSERT with native SQL

MusAbaper — Tue, 23 Jun 2009 19:53:09 GMT

Hi,

Can someone give me an example for :

---> DELETE all from a specific table.

---> INSERT a lot of records in a specific table

Thank you in advance.

Best regards

Re: DELETE & INSERT with native SQL

Former Member — Tue, 23 Jun 2009 20:02:12 GMT

hey..

try these..

DELETE FROM <database table > WHERE <sql_cond >.

DELETE FROM <database table > WHERE <sql_cond > FROM < source internal table>.

INSERT INTO <database table> VALUES <sourcetable> .

INSERT <database table> FROM <sourcetable>.

Regards,

Re: DELETE & INSERT with native SQL

Former Member — Wed, 01 Jul 2009 00:36:33 GMT

hi,

the statement : insert into <TableName> values (Source)

how can I know if the data have already insert to the database table?

now I have a program have this problem:

data: dbs like dbcon-con_name value 'HRGK',
        dbtype type dbcon_dbms.
data: sqlerr_ref type ref to cx_sql_exception,
         exc_ref    type ref to cx_sy_native_sql_error,
         error_text type string.
data: cnt type i,
        i(2) type c.
set locale language 'E'.
EXEC SQL.
  CONNECT TO :DBS
ENDEXEC.
EXEC SQL.
  SET CONNECTION :DBS
ENDEXEC.
EXEC SQL.
  SELECT COUNT(*) from org_unit into :cnt
ENDEXEC.
write: / cnt.
*do.
i = 'A'.                
try.
    EXEC SQL.
      BEGIN
       insert into ORG_UNIT ( ORGAN_ID, UNIT_NO, UNIT_NAME, PARENT_ID)
                     values ( 'A', '123412', 'SDFSG', 'DDS');                     " ORGAN_ID is primary key
"If the data 'A' has already exist in database ORG_UNIT, it will show error message
" But if I use :I instead 'A', it will not show error message.

        IF SQL%FOUND THEN
           COMMIT;
        END IF;
      END;
    endexec.

    EXEC SQL.
      SELECT COUNT(*) from ORG_UNIT into :cnt
    ENDEXEC.

    write: / cnt.

 catch cx_sy_native_sql_error into exc_ref.
    error_text = exc_ref->get_text( ).
    write: / error_text.

  catch cx_sql_exception into sqlerr_ref.
    perform handle_sql_exception using sqlerr_ref.

endtry.

EXEC SQL.
  disconnect :DBS
ENDEXEC.

*enddo.

*&---------------------------------------------------------------------*
*&      Form  handle_sql_exception
*&---------------------------------------------------------------------*
form handle_sql_exception
  using p_sqlerr_ref type ref to cx_sql_exception.

  format color col_negative.
  if p_sqlerr_ref->db_error = 'X'.
    write: / 'SQL error occured:', p_sqlerr_ref->sql_code,
           / p_sqlerr_ref->sql_message.                     "#EC NOTEXT
  else.
    write:
      / 'Error from DBI (details in dev-trace):',
        p_sqlerr_ref->internal_error.                       "#EC NOTEXT
  endif.

endform.                    "handle_sql_exception

please help to see the words:

*" If the data 'A' has already exist in database ORG_UNIT, it will show error message*

*" But if I use :I instead 'A', it will not show error message.*

in the program.

Thanks a lot!

Edited by: hshteld on Jul 1, 2009 2:49 AM

topic DELETE & INSERT with native SQL in Application Development and Automation Discussions

DELETE & INSERT with native SQL

Re: DELETE & INSERT with native SQL

Re: DELETE & INSERT with native SQL