See how to download an SAP table structure (i.e., the data types of each field), convert it into data types of the underlying database, and transfer table data with a large number of records. Use Object Linking and Embedding objects to concoct the database, create the table, and transfer data. This method uses Microsoft SQL Server 2005 Express Edition for this purpose.
Key Concept
You can use Object Linking and Embedding automation objects to connect with an external database and transfer information between the two landscapes. Using Microsoft Active data objects you can connect and execute SQL statements. The result is to transfer table definition and data to the underlying database. This transferred data can be used for multiple purposes such as reporting and data warehousing, and to use BI tools. 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:
- Get the information of table schema from the SAP system.
- Build a SQL statement to create a table in the database.
- Get back the information about the fields and their types from the newly created table.
- Get all records from the SAP table and fetch them into internal table.
- Loop through records and build an insert query to append records in this table.
- 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.