Creating a Database Table

In the earlier section, we saw the steps to import the Database table into Informatica.

Informatica PowerCenter tools allow you to create a database table from Informatica itself. If you have a table structure available in PowerCenter but the corresponding table is not available in database, you will not be able to use the table for your mapping purpose as you will not be able to provide the required connections for loading the data. PowerCenter provides a very efficient and faster way to generate a database table directly. So you need not write a CREATE SQL statement to generate a table in your Database.

For our reference, we will create a table named TGT_EMPLOYEES in oracle database.

1. In the Designer, got o Tools | Target Designer to open the Target Designer.
2. In the workspace, select the TGT_EMPLOYEES target definition.
3. Go to Targets | Generate/Execute SQL.

1. The Database Object Generation dialog box appears.
2. In the File Name field, enter the following text to generate an SQL script called MKTABLES.SQL - C:\MKTABLES.SQL. You can provide any path. Informatica PowerCenter will generate the SQL file at the specified location.
   
3. Select the ODBC data source to connect to the target database.
4. Enter the necessary username and password, and then click on Connect:

1. Select the appropriate option for generating a table:
   
2. Click on Generate and execute.
3. Click on Close to exit the Database Object Generation dialog box.

With this, the table TGT_EMPLOYEES will be generated in the Oracle database, which can be cross-checked by writing a query in the database.