Background documentationEXPORT TABLE Command Locate this document in the navigation structure

 

You can use the Loader command EXPORT TABLE to export the data from a specified table.

Prerequisites

The table specified must exist in the database. Only the owner of the table can execute the EXPORT TABLE ... command with the format PAGES. Otherwise you must have access authorization for the tables you want to export.

Structure

Syntax Syntax

  1. <export_table_command> ::=
      EXPORT TABLE <table_name>
        [<order_clause>]
        <catalog_data_outstream_table_spec>
        [<package_spec>]
    
    <order_clause> ::=
      ORDER BY <column_name>,...
    
    <catalog_data_outstream_table_spec> ::=
      <catalog_outstream_spec>
    | <data_outstream_table_spec>
    | <catalog_outstream_spec> <data_outstream_table_spec>
    <catalog_outstream_spec> ::=
      CATALOG <outstream_ddl_spec>
    <data_outstream_table_spec> ::=
      <data_outstream_part_spec>
    | <data_outstream_column_spec>
    <data_outstream_part_spec> ::=
      DATA <outstream_tableformat_spec>
    <data_outstream_column_spec> ::=
      DATA <outstream_columnformat_spec> [<export_lobcolumn_spec> ...]
    
    <package_spec> ::=
      PACKAGE <oustream_csv_spec>
End of the code.

You can use the EXPORT TABLE command to export all the data from a specified table. You cannot select specific column values with the EXPORT TABLE command. You can only do this with the EXPORT COLUMNS command.

<catalog_outstream_spec>, <data_outstream_table_spec>

Depending on whether database catalog and/or application data is exported, the following data streams are generated during the export:

  • Data stream for the database catalog definitions CATALOG <outstream_ddl_spec>

  • Data stream for the application data DATA <outstream_tableformat_spec>

System table SYSLOADER.TRANSFORMATIONMODEL and additional system tables are filled.

See: Data Stream

Syntax Syntax

  1. EXPORT TABLE <table_name>
      [<order_clause>]
      <catalog_outstream_spec> <data_outstream_table_spec>
      [<package_spec>]
End of the code.

This EXPORT TABLE command exports the data from the specified table (database catalog and application data).

Syntax Syntax

  1. EXPORT TABLE <table_name>
      <catalog_outstream_spec>
      [<package_spec>]
End of the code.

This EXPORT TABLE command exports the database catalog information from the specified table.

Syntax Syntax

  1. EXPORT TABLE <table_name>
      [<order_clause>]
      <data_outstream_table_spec>
      [<package_spec>]
End of the code.

This EXPORT TABLE command exports the application data from the specified table.

More information about the syntax element data_outstream_table_spec:

<data_outstream_part_spec> ::= DATA <outstream_tableformat_spec>

To export the data in PAGES or RECORDS format, use syntax variant DATA <outstream_tableformat_spec>. We recommend this option if you do not need the data in a readable format and you want to move it from one SAP MaxDB database to another. All the data is exported in data streams, including the LOB data. After it has been exported, you can no longer change the data in the data stream. A table is read-only during an export in data format PAGES.

<data_outstream_column_spec> ::= DATA <outstream_columnformat_spec> [<export_lobcolumn_spec>...]

To export the data in CSV or FWV [BINARY] format, use syntax variant DATA <outstream_columnformat_spec>. We recommend this option if you need the data in a readable form, for example for tools of other providers. It is possible to move the data from one SAP MaxDB database to another, but this takes much longer than if you use RECORDS or PAGES format. You can still change the data in the data streams after it has been exported. If you want to export LOB values, you need to specify a separate data stream for these column values. You can do this with the export_lobcolumn_spec syntax element (Exporting LOB Values).

<order_clause>

You can use the order_clause to specify the sort sequence for the table rows in the data stream. The columns specified in the order clause define the sort sequence.

<package_spec>

You can export system table SYSLOADER.TRANSFORMATIONMODEL.

  • If you specify a data stream using PACKAGE <outstream_csv_spec>, all entries in the SYSLOADER.TRANSFORMATIONMODEL table are exported to this data stream. We recommend you use this option to create a log of the export or import process.

  • If you do not specify a data stream using PACKAGE <outstream_csv_spec>, the system table SYSLOADER.TRANSFORMATIONMODEL is not exported.

See: Additional Log Functions (package_spec)

Result

The application data and/or the database catalog are exported to the data_outstream_table_spec and catalog_outstream_spec data streams. If necessary, all entries in the TRANSFORMATIONMODEL table are exported to the package_spec data stream. You can use the corresponding IMPORT command to restore the database catalog and/or the application data.

Errors: The export cannot be completed successfully. Loader logs the cause of the error in the log file. The values PACKAGEGUID and CLIENTNODE are also written to the log file. The PACKAGEGUID is needed if you want to restart the export.

Example

Loader Tutorial, EXPORT TABLE