Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Write Accesses →  INSERT, UPDATE, MODIFY, DELETE - target →  INSERT, UPDATE, MODIFY, DELETE dbtab - CLIENT, CLIENTS 

INSERT, CLIENT

This example demonstrates client handling in the ABAP SQL statement INSERT.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    TYPES clients TYPE RANGE OF mandt.

    DATA(out) = cl_demo_output=>new( ).

    DELETE FROM demo_clnt USING ALL CLIENTS.
    DELETE FROM demo_no_clnt.

    INSERT demo_clnt CLIENT SPECIFIED FROM TABLE
      @( VALUE #( ( mandt = '000' id = 1 num = 1 )
                  ( mandt = '000' id = 2 num = 2 )
                  ( mandt = '000' id = 3 num = 3 )
                  ( mandt = '000' id = 4 num = 4 )
                  ( mandt = '000' id = 5 num = 5 )
                  ( mandt = '100' id = 1 num = 1 )
                  ( mandt = '100' id = 2 num = 2 )
                  ( mandt = '100' id = 3 num = 3 ) ) ) .

    INSERT demo_no_clnt FROM TABLE
      @( VALUE #( ( id = 1 num = 1 )
                  ( id = 2 num = 2 )
                  ( id = 3 num = 3 )
                  ( id = 4 num = 4 ) ) ).

    out->next_section( `Subquery USING CLIENT` ).
    SELECT FROM demo_no_clnt AS nocl
                LEFT OUTER JOIN demo_clnt AS cl ON nocl~id = cl~id
                USING CLIENT '100'
                FIELDS cl~mandt,
                       nocl~id,
                       cl~num
                INTO TABLE @DATA(subquery1).
    out->write( subquery1 ).

    out->next_section( `INSERT ( USING CLIENT )` ).
    DELETE FROM demo_clnt_target USING ALL CLIENTS.
    INSERT demo_clnt_target USING CLIENT '100' FROM
      ( SELECT FROM demo_no_clnt AS nocl
                 LEFT OUTER JOIN demo_clnt AS cl ON nocl~id = cl~id
                 USING CLIENT '100'
                 FIELDS nocl~id,
                        cl~num  ) .
    SELECT FROM demo_clnt_target  USING CLIENT '100'
           FIELDS *
           INTO TABLE @DATA(result1).
    out->write( result1 ).

    out->next_section( `Subquery USING CLIENTS` ).
    SELECT FROM demo_no_clnt AS nocl
             LEFT OUTER JOIN demo_clnt AS cl ON nocl~id = cl~id
             USING CLIENTS IN @( VALUE clients(
                                  ( sign = 'I'
                                    option = 'EQ'
                                    low = '100' ) ) )
           FIELDS cl~mandt,
                  nocl~id,
                  cl~num
           INTO TABLE @DATA(subquery2).
    out->write( subquery2 ).

    out->next_section( `INSERT ( USING CLIENTS IN )` ).
    DELETE FROM demo_clnt_target USING ALL CLIENTS.
    TRY.
        INSERT demo_clnt_target CLIENT SPECIFIED FROM
          ( SELECT FROM demo_no_clnt AS nocl
                     LEFT OUTER JOIN demo_clnt AS cl ON nocl~id = cl~id
                     USING CLIENTS IN @( VALUE clients(
                                          ( sign = 'I'
                                            option = 'EQ'
                                            low = '100' ) ) )
                    FIELDS cl~mandt,
                           nocl~id,
                           cl~num ) ##null_values.
        SELECT FROM demo_clnt_target USING CLIENT '100'
               FIELDS *
               INTO TABLE @DATA(result2).
        out->write( result2 ).
      CATCH cx_sy_open_sql_db INTO DATA(exc).
        out->write( exc->get_text( ) ).
    ENDTRY.

    out->next_section( `INSERT ( USING CLIENTS IN ) with Correction` ).
    DELETE FROM demo_clnt_target USING ALL CLIENTS.
    TRY.
        INSERT demo_clnt_target CLIENT SPECIFIED FROM
          ( SELECT FROM demo_no_clnt AS nocl
                     LEFT OUTER JOIN demo_clnt AS cl ON nocl~id = cl~id
                     USING CLIENTS IN @( VALUE clients(
                                         ( sign = 'I'
                                           option = 'EQ'
                                           low = '100' ) ) )
                   FIELDS COALESCE( cl~mandt, '100' ),
                          nocl~id,
                          cl~num ).
        SELECT FROM demo_clnt_target USING CLIENT '100'
               FIELDS *
               INTO TABLE @DATA(res3).
        out->write( res3 ).
      CATCH cx_sy_open_sql_db INTO DATA(exc1).
        out->write( exc1->get_text( ) ).
    ENDTRY.

    out->display( ).

Description

The example demonstrates how the additions USING CLIENT and CLIENT SPECIFIED are used in INSERT statements that use various subqueries as data sources. The example uses two database tables, the client-specific table DEMO_CLNT and the cross-client table DEMO_NO_CLNT, which themselves are associated in the subqueries by a join.

  • First, DELETE statements are used to delete all data in both database tables without specifying a condition or a source. For the client-specific table, the addition USING ALL CLIENTS is used to delete the data of all clients.
  • INSERT statements are then used to fill both tables with data from internal tables constructed in a host expression. For the client-specific table, the addition CLIENT SPECIFIED is used to respect the client IDs in the client column of the internal table.
  • The output shows the results set of the query used as a subquery in the following INSERT statement. This query uses the addition USING CLIENT to ensure that data from the client 100 is read. A LEFT OUTER JOIN between the client-specific table and cross-client table produces a null value in the client column.
  • When the results set of the subquery is inserted, INSERT also uses the addition USING CLIENT. This ensures that the content of the client column of the subquery is overwritten by the value 100 for each row. This means that the null value itself is ignored. The result is shown in the output.
  • The next output shows the results set of a further query that uses the addition USING CLIENTS IN @rtab instead of USING CLIENT. The ranges table also selects only the client 100, which means that the result is the same as in the preceding query.
  • There is, however, a difference in the way the query is used as a subquery in an INSERT statement. USING CLIENTS IN @rtab is interpreted to mean that the client ID of the subquery is inserted, which is why implicit client handling by the INSERT statement using CLIENT SPECIFIED has to be switched accordingly. The client column of the subquery, however, contains a null value and an exception is raised accordingly.
  • The final INSERT statement bypasses the exception by using the COALESCE function in the SELECT list to replace the null value with the client ID 100.