topic Insert data from Standard DBtable to another DBTtable in Application Development and Automation Discussions

Insert data from Standard DBtable to another DBTtable

Former Member — Sat, 11 Apr 2020 10:58:59 GMT

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.

Re: Insert data from Standard DBtable to another DBTtable

Sandra_Rossi — Sat, 11 Apr 2020 11:09:34 GMT

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

Re: Insert data from Standard DBtable to another DBTtable

Former Member — Sat, 11 Apr 2020 11:13:01 GMT

Thanks Rossi, Can you plz show me the steps ?

Re: Insert data from Standard DBtable to another DBTtable

geert-janklaps — Sat, 11 Apr 2020 11:24:19 GMT

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

Re: Insert data from Standard DBtable to another DBTtable

Former Member — Sat, 11 Apr 2020 12:13:47 GMT

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.

Re: Insert data from Standard DBtable to another DBTtable

geert-janklaps — Sat, 11 Apr 2020 12:34:49 GMT

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.

Re: Insert data from Standard DBtable to another DBTtable

venkateswaran_k — Sat, 11 Apr 2020 12:37:49 GMT

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.

Re: Insert data from Standard DBtable to another DBTtable

Former Member — Sat, 11 Apr 2020 12:50:59 GMT

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.

Re: Insert data from Standard DBtable to another DBTtable

Sandra_Rossi — Sat, 11 Apr 2020 13:43:42 GMT

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".

Re: Insert data from Standard DBtable to another DBTtable

Sandra_Rossi — Sat, 11 Apr 2020 13:45:14 GMT

Solution 1: SELECT and INSERT.

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

Re: Insert data from Standard DBtable to another DBTtable

Former Member — Sat, 11 Apr 2020 13:48:46 GMT

The only column is mandt.

Should add it in the "select" ?

Re: Insert data from Standard DBtable to another DBTtable

matt — Sat, 11 Apr 2020 14:30:10 GMT

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