Table of Contents


  1. Preface
  2. Using the Designer
  3. Working with Sources
  4. Working with Flat Files
  5. Working with Targets
  6. Mappings
  7. Mapplets
  8. Mapping Parameters and Variables
  9. Working with User-Defined Functions
  10. Using the Debugger
  11. Viewing Data Lineage
  12. Comparing Objects
  13. Managing Business Components
  14. Creating Cubes and Dimensions
  15. Using the Mapping Wizards
  16. Datatype Reference
  17. Configure the Web Browser

Creating a Target Table

Creating a Target Table

After you add a relational target definition to the repository, you can instruct the Designer to generate and execute the SQL code to create the target in a relational database. You cannot create a table in a relational database from an XML target definition or a flat file target definition.
The Designer generates the SQL script using characters in the UCS-2.
If the target already exists in that database, you can drop it and re-create it. The Designer writes the SQL code to an .SQL text file, so you can review and edit the DDL commands by opening this file.
To generate and execute the SQL code:
  1. In the Target Designer, select the relational target definition you want to create in the database. If you want to create multiple tables, select all relevant table definitions.
  2. Click Targets > Generate/Execute SQL.
    Click Connect and select the database where the target table should be created. Click OK to make the connection.
    Enter a file name and location for the SQL script you are about to generate and any options you want to include in the SQL DDL code. This text file exists on the local file system, not in the repository.
    Depending on the Generation options you select, the SQL script will contain all of the CREATE and DROP commands that match the selections. For example, if you created a target definition with primary keys, choose to generate the SQL with primary keys.
  3. Click Generate SQL File if you want to create the SQL script, or Generate and Execute if you want to create the file, and then immediately run it.
    When you click Generate SQL file, the SQL generated for the selected table definitions is stored in the file you selected. If the file already exists, a dialog box appears prompting you to overwrite the existing file. The progress of copying the generated SQL file appears in the Output window in the Designer.
    After the file has been generated, you can click Edit SQL File, which opens a text editor so you can modify the SQL statements. When the Designer generates the SQL file for the target database, it encloses all table and field names containing the slash character in double quotes.
    You can click Execute SQL File to create the tables. When you click Generate and Execute, the SQL generated for the selected table definitions is stored in the file you selected and immediately executed.
    As long as the Designer is open, it locks the SQL file you last opened and modified. If you want to unlock the file so that you can view it in a different application, open a different SQL file in the Designer, or exit the Designer.
  4. Click Close.
    When you close this dialog box, the Designer maintains an open connection to the target database. If you reopen the dialog box, you do not need to reconnect to the target database.

Updated July 02, 2020