There are times when you need to transport a data structure and data to other databases to archive data or to use decision support systems such as BI tools.

The utility ABAP program I describe here is cost effective as you do not need to purchase costly data transferring tools. You can use it to transfer data from one or many tables in one session. The progress of the transfer of the structure and data is shown in the front end and a log is generated for the completion of each step.

Unlike other utilities that are available, this one creates a table in the underlying database with primary keys and converts compatible data types. You can use it with SAP NetWeaver 7.0 and later, and you can use it to transfer a batch of tables in one go.

In addition to table data, this utility also transfers the metadata of tables. If there is no table on the database side it creates a table in the database and then starts the transfer of data from SAP tables to the underlying database. This program gets the metadata of tables from the SAP system using a function module. It converts this metadata into proper SQL statements to create a table in the database.

The data is then transported using SQL statements. Object Linking and Embedding (OLE) objects connect and execute the SQL statements. Log entries are generated during the process and can be viewed by browsing the log table in the database. This transported data can be used by BI systems to generate analytical reporting.

Here is the step-by-step procedure to create the ABAP program and front-end screen. Go to transaction code SE38. Name the program Z_Data_Transfer as shown in Figure 1.

Figure 1

Use transaction code SE38 to create the ABAP program

Click the Create button and the ABAP editor opens. Write the code shown in Figure 2. The structure defined here named ST_ITAB is used for the list of tables to be transported. The first field is MARK. The MARK field is used to select an entry in the table. TableName is used for tables.

Figure 2

Initial variable and type declarations in the ABAP editor

Double-click in the call screen CALL SCREEN 100 exactly at 100. A message appears to create a screen. Click Yes. It takes you to the screen painter as shown in Figure 3.

Figure 3

Screen properties where you write a short description

Next click the Layout button on the toolbar. A new empty layout designer opens (Figure 4).

Figure 4

Layout designer

Click the Table control wizard icon to create a table on a screen. The wizard starts as shown in Figure 5.

Figure 5

Initial screen of the table control wizard

Click the Continue button. The next screen asks for the control name (Figure 6). This name will be used to define the table control object.

Figure 6

Enter the table control name

Manually enter the table control name (Z_TABLE). Click the Continue button to go to Figure 7. Do not use any special characters and do not start names with numbers.

Figure 7

Select the internal table

Internal table ITAB and the work area are already defined in Figure 2. In Figure 7, press the F4 key to select the internal table that you defined. Check the Table work area check box, and press F4 to select the work area you defined. The program status should be active to get the internal table name and work area name; use Ctrl+ F3 to activate.

Click the Continue button to move to the next screen (Figure 8).

Figure 8

Select the fields to display in table control

Fields of the internal table are displayed here. Select the TABLENAME field that is displayed in your table control. Other fields are used for row selection. Click the Continue button to move to Figure 9.

Figure 9

Table control attributes

Click the Line selection col. check box and move your curser into the Selection col. fld field. Press F4 and select MARK. Click the Continue button to go to Figure 10.

Figure 10

Do not change anything

Do not change anything in Figure 10; in this example you do not need scrolling. Click the Continue button to get to Figure 11.

Figure 11

The program name is automatically entered

The program name automatically appears in this screen in all the include fields. You do not need to change anything. Click the Continue button to move to Figure 12.

Figure 12

Wizard completion screen

Click the Complete button in Figure 12 and the wizard closes. Now the screen looks like what you see in Figure 13.

Figure 13

Table control final design

Add three buttons, one by one, on the screen, and one text box to add or remove table names in the table control as shown in Figure 14.

Figure 14

Define this text type variable in the data declaration area of your code

Click the Layout button as shown in Figure 3 and the next screen is the same as shown in Figure 13. Press F6 and a window appears (Figure 15). Click the Get from Program button and select your variable, which in my example is TEXT_TABLEN.

Figure 15

Select TEXT_TABLENAME from list

Click the enter icon and drag the text box to the position where you want to place it as shown in Figure 16.

Figure 16

Place a text box on the screen

On the left side of the toolbox drag a button control to the screen. Enter its name as TR_ADD. In the Text field, enter Add_Table_Name, and enter ADD in the FctCode (function code) field (Figure 17). Add two more buttons from the toolbar to the screen as described above.

Figure 17

Add a button on the screen and enter a name

Repeat this process to add two more buttons and name them as follows:

• Name BTN_REMOVE, Text Remove Table Name, Function Code REM
• Name BTN_TRANSFER, Text Connect and start …., Function Code: TR

Position the buttons as shown in Figure 18.

Figure 18

Final design of the screen

Add a toolbar to this application called PF-STATUS. In ABAP this toolbar is used for navigation in the application (such as going back or exiting). To add the toolbar close the layout editor and it takes you to the screen properties window (seen in Figure 3). Click the Flow logic tab. Double-click the MODULE STATUS_0100 statement in the ABAP code to add PF-STATUS (Figure 19).

Figure 19

Add PF-STATUS

The window in Figure 20 appears. Click the Yes button to create the toolbar PF-STATUS.

Figure 20

Create an object module STATUS1_0100 in program

Select a program name in this window (Figure 21).

Figure 21

Select a program name and click the enter icon

The code for the Process before output module shown in Figure 22 is then added to your program. Process before output means the module is executed before displaying the screen output.

Figure 22

Process before output module

Remove * from commented lines. Name PF-STATUS as ‘ZSTATUS’ and the title name as ‘ZTITLE’. The code then looks like what you see in Figure 23.

Figure 23

Name the toolbar PF-STATUS and title here

Now you need to name the toolbar. Double-click PF-STATUS in Figure 23. A screen appears asking if you want to create an object (Figure 24).

Figure 24

Create object PF-STATUS (tool bar)

Click the Yes button and you see the screen shown in Figure 25.

Figure 25

PF-STATUS creation window

Enter the name of the toolbar in the Short Text field (Figure 25). Click the enter icon, and the screen in Figure 26 appears.

Figure 26

PF-STATUS

Click the Function Keys + sign to open the complete standard tool bar. Enter BACK in the green arrow icon field, EXIT in the yellow icon field, and CANCEL in the red icon field. Press Ctrl + S to save. Activate the PF-STATUS by pressing Ctrl + F3. Now go back to the code as shown in Figure 23 and double-click ZTITLE. A pop-up Create Object screen appears to confirm (Figure 27). Click the Yes button.

Figure 27

Create Object pop-up window

In the next window that appears enter Data Transfer utility in the Title name field (Figure 28).

Figure 28

Create the title of the application window

Click the enter icon. Press Ctrl + S to save and Ctrl + F3 to activate. Now go back to the ABAP editor and find CALL SCREEN 100 as shown in Figure 2. Double-click 100. In the screen that appears, go to the Flow logic tab (Figure 29).

Figure 29

Process after input

To program the actions of the toolbar and the icons that you just added to the screen, double-click MODULE USER_COMMAND_0100 in Figure 29. This takes you to Figure 30.

Figure 30

Create Object window

In Figure 30, click the Yes button. In the next window select the program name where you program this module (Figure 31), and click the enter icon. This results in the screen in Figure 32, where you then insert the code.

Figure 31

Create the PAI module

Figure 32

Process after input for screen 0100

Define another variable for user commands as shown in Figure 33.

Figure 33

Ok_code for user commands

Figure 34 shows the code for this module.

Figure 34

Code for the toolbar and icons

You have programmed two buttons: Add Table Name and Remove Table Name. You can add as many tables as require transferring. You now program a subroutine named Transfer Data using OLE objects. For this, you add the statement in Figure 35 in the main program declaration.

Figure 35

Include OLE objects

Get the technical information of the table to be transported using function module RFC_READ_TABLE. For this purpose you have to define the structures and tables in Figure 36.

Figure 36

Code to get metadata of table

Now there is enough information to create a table in the underlying database. To connect with the database using OLE objects you define the following variables (Figure 37).

Figure 37

Define OLE objects

To connect with the Microsoft SQL server you have to create a data source name (DSN) connection string in the Windows control panel. In Windows, click the Start button and follow menu path Control Panel > Administrative tools > Data Sources (ODBC). An ODBC administrator window opens (Figure 38).

Figure 38

ODBC main window

Click the Add. button. A new window appears (Figure 39).

Figure 39

Windows Create New Data Source screen

Select the SQL Server entry by clicking on it, click the Finish button, and in the next window (Figure 40) enter the data source Name, SQLSERVER. Click the Next button to go to Figure 41.

Figure 40

Create a DSN connection

Figure 41

Authentication mode

In this window select the  With Windows NT authentication using the network login ID radio button. Click the Next button, select the Change the default database to: check box, and set the default database (SAP) where you want to transfer the tables, as shown in Figure 42.

Figure 42

Select the default database

Click the Next button and then an entry with the name SQLSERVER is created as the data source. Now you create the connection object (Figure 43):

Figure 43

Set up the connection string and open the connection

Create a table check to see if the table is already created in the database by using the code in Figure 44.

Figure 44

Check the table’s existence in the database

The query executes, and if it returns a row in a recordset object (“rs” below) then it means that the table already exists. If the EOF property of the recordset object is true then it means no table was found. Get the property of EOF of the OLE object to see that if table exists (Figure 45).

Figure 45

If value of rs_eof is 1 then create new table

Start building a query statement to create a new table in the database:

CONCATENATE  'CREATE TABLE ' query_table ' ('  INTO sql SEPARATED BY Space.

Define two variables for field length as character and NotUsed in the subroutine (Figure 46). The type should be character in order to use it in strong operations.

Figure 46

Code to build an SQL statement

The table structure is built, so you can execute this query, creating a table in the database. Send a success message to the user as shown in Figure 47.

Figure 47

Send message to user

Define the dynamic internal table and work area to get all the rows into the internal table (Figure 48).

Figure 48

Define dynamic table and work area definition

Create the itab_ref structure for reference (Figure 49). Reference variables contain data references. You create the data type of a data reference variable using the create data statement.

Figure 49

Creation of dynamic internal table and work area

Now you have a dynamic internal table and work area to get all records from the SAP system. Use a select statement to fetch all records from SAP table into this internal table (Figure 50).

Figure 50

Fetch records into internal table

If the table you are downloading has some previous downloaded rows then delete all rows using the SQL statement Delete From as shown in Figure 51.

Figure 51

Delete all rows from table in the underlying database

In Figure 51 you define another internal table for the purpose of field names and its data type as shown in Figure 52.

Figure 52

Internal table definition for field names and data types

You fill this table from the Microsoft SQL server database. Connect to the server and get the table field names and their data types in the newly created or already created table. Get the structure of the newly created table from the Microsoft SQL server (Figure 53).

Figure 53

Fill internal table with field names and data type

Get the field object from object recordset at ordinal position 0 (Figure 54).

Figure 54

Get first column name

Again get the field object from object recordset at ordinal position 1 (Figure 55).

Figure 55

Get the data type

Get the value of this field object into variable ftype (Figure 56).

Figure 56

Get the field data type

In Figure 46 you replaced a special character ‘/’ with ‘__’ (double underscore). Now replace it back (Figure 57).

Figure 57

Replace character

Assign values to internal table fields and append them to the table (Figure 58).

Figure 58

Assign field name and field type

Call method MoveNext of the object objrs to move forward to the next record (Figure 59).

Figure 59

Move to Next Row

Refresh the rs_eof variable again from the recordset object (Figure 60).

Figure 60

Get the value of EOF property of recordset object

You now have all the field names and data types of the fields of the newly created table in the database. This information is used to build a Microsoft SQL statement to insert records in the database. I begin this statement in Figure 61.

Figure 61

Start building Microsoft SQL statement to insert a row

Loop through field names that you have already filled in the internal table to get the values dynamically from the main table <itab> (Figure 62).

Figure 62

LOOP on internal table rows

This assigns the field value to <dyn_field> dynamically and adopts the data type automatically (Figure 63).

Figure 63

Assigning column of to

Remove blanks from the fvalue (Figure 64).

Figure 64

Remove blanks

Here you checking to see if the field type is numeric or integer; if this is the case then there is no need to use single quotes. Otherwise—if the field type is a special character or letter—you need to use them on both sides for the character value (Figure 65).

Figure 65

Single quotes addition in Microsoft SQL statement if field type is non-numeric

All fields are complete for one record of the database table. Remove the last character “,” from the SQL string because you have extra commas during the building of the SQL statement (Figure 66).

Figure 66

Removal of Extra character “,”

Close the insert statement and add a semicolon (Figure 67).

Figure 67

Closing insert statement

Execute this statement to insert the record (Figure 68).

Figure 68

Execute the SQL Statement

To show the percentage of progress you calculate number of rows inserted divided by total number of rows (Figure 69):

Figure 69

Function to show progress on status bar

Send a message that the table transport is successful (Figure 70).

Figure 70

Send success message for table transfer

The complete code is available at the end of this article. It shows how to:

1. Get the information of table schema from the SAP system.
2. Build a SQL statement to create a table in the database.
3. Get back the information about the fields and their types from the newly created table.
4. Get all records from the SAP table and fetch them into internal table.
5. Loop through records and build an insert query to append records in this table.
6. Terminate the query properly and insert the record.

Fahim Ahmad

Fahim Ahmad is a senior ABAP consultant for Descon Engineering Ltd. He has master’s degrees in statistics and computer science from the University of Punjab in Lahore, Pakistan. He has 10 years of experience as a software engineer. Fahim specializes in ABAP enhancements and reports, workflow, RFC programming, interfacing, OLE, and SAP Web services configuration and development.

You may contact the author at fahim119@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.