Insert data from Standard DBtable to another DBTtable

[Former Member]

Hello All,

I need to insert data from 2 Standards Tables (KNA1 & adrc) to a ZDBTable.

zdbtable has just 4 columns (3 from kna1 and 1 from adrc).

I need to know what is the recommended way. Using Internal tables with or without header...

PS: I'm a beginner on ABAP.

thanks all.

[Sandra_Rossi]

Header lines are obsolete (anyway, you should code using ABAP Objects, header lines are not permitted in classes)

[Former Member]

Thanks Rossi, Can you plz show me the steps ?

[geert-janklaps]

Hi,

You could use a selection like this (adapt to your own needs):

SELECT c~kunnr, c~name1, a~country
  FROM kna1 as c
  INNER JOIN adrc AS a ON a~addrnumber = c~adrnr
  INTO TABLE @DATA(lt_customer_data)
  WHERE a~date_from <= @sy-datum
  AND a~date_to >= @sy-datum.

I wouldn't suggest to copy this data to a new z-table, because you'll be storing data twice (and you'll probably run into discrepancies between the actual data and your z-table.

If the only reason you're creating the separate z-table is ease of access to the data, I would suggest to create either a database view or a CDS-view (if you have CDS available, this is the best way). In this way you'll not be storing the data twice, you won't have to worry about discrepancies and you can easily access the data you need. (you can use the selection above to create the view)

If you're going to read data into internal tables, you shouldn't be using header lines anymore since those are obsolete.

Best regards,

Geert-Jan Klaps

[Former Member]

Hi Jan,

What I should put in "@DATA(lt_customer_data)" ?

it is a line type of ztable ? like this :

types:  begin of line_type,
          client      like ztclient99-client,
          client_id   like ztclient99-client_id,
          name_last   like ztclient99-name_last,
          name_first  like ztclient99-name_first,
          addrnumber  like ztclient99-addrnumber,
        end of line_type.

[geert-janklaps]

If your system doesn't support the inline data declarations you'll have to do something like this:

types: begin of line_type,
  kunnr like kna1-kunnr,
  name1 like kna1-name1,
  country like adrc-country,
end of line_type.

DATA: lt_customer_data TYPE TABLE OF line_type.

SELECT c~kunnr c~name1 a~country
  FROM kna1 as c
  INNER JOIN adrc AS a ON a~addrnumber = c~adrnr
  INTO TABLE lt_customer_data
  WHERE a~date_from <= sy-datum
  AND a~date_to >= sy-datum.

[venkateswaran_k]

Dear Khalil

I assume your table is Ztclient99. And I assume you copy records based on selection criteria of Customer code.

types:  begin of line_type,
          client      like ztclient99-client,
          client_id   like ztclient99-client_id,
          name_last   like ztclient99-name_last,
          name_first  like ztclient99-name_first,
          addrnumber  like ztclient99-addrnumber,
        end of line_type.

DATA : it_data type stndard table of ztclient99,
       wa_data type ztclient99.

"get range of customers from the selection screen in s_kunnr.

SELECT A~KUNNR AS 'CLIENT_ID', A~NAME1 AS 'NAME_LAST', A~NAME2 AS 'NAME_FIRST', B~ADRNR AS 'ADDRNUMBER'
FROM KNA1 AS A 
INNER JOIN ADRC AS B ON a~addrnumber = B~adrnr
INTO CORRESPONDING FIELDS OF TABLE IT_DATA
WHERE A~KUNNR IN S_KUNNR.

"SAVE DATA IN TABLE
IF IT_DATA[] IS NOT INITIAL.
MODIFY ztclient99 FROM TABLE IT_DATA.
ENDIF.
 

[Former Member]

This is my code, It works but i get a warning :

The work area "LT_CLIENT99" has more fields than selected.

data:  lt_client99 type table of ztclient99.

select k~kunnr as client_id k~name1 as name_last k~name2 as name_first a~addrnumber as addrnumber
  up to 220 rows
  from kna1 as k
  inner join adrc as a
  on a~addrnumber = k~adrnr
  into corresponding fields of table lt_client99
  where a~addrnumber = k~adrnr.

if sy-subrc = 0.
  insert ztclient99 from table lt_client99.
endif.

[Sandra_Rossi]

Guess why you get the warning:

The work area "LT_CLIENT99" has more fields than selected.

Look at the number of columns of database table ZTCLIENT99, and compare with the 4 columns you indicate after "select".

[Former Member]

The only column is mandt.

Should add it in the "select" ?

[Sandra_Rossi]

Solution 1: SELECT and INSERT.

Solution 2 from ABAP 7.50: INSERT ... FROM ( SELECT ... )

[matt]

Use a view. Why replicated the data? You only have to keep it up to date. A view is always up to date.