In this article, we will show you how to create a target table in Informatica and use it as the destination/target table for the transformed data with an example. For example, If we don’t have a table structure that matches our requirement, then this option can be beneficial.
How to Create Target Table in Informatica
Before we start to Create Target Table in Informatica, First connect to Informatica repository service with your Admin credentials and then Navigate to Target Designer.
TIP: Target Menu in the Menu bar available only when you are in the Target Designer section.
Next, Please navigate to Targets menu in Menu Bar. From the following screenshot, you can observe the list of available targets inside the Informatica PowerCenter Designer. In this example, our task is to create a target table from scratch. So, we are selecting the Create option.
Once you select the Create option, a new window called Create Target table will be displayed as shown below:
- Enter a new name for this table: Please provide the unique name for this new table. We assigned Target_Multicast as the new name for this table
- Select a database type: Please select the required database type from the list of available databases. Here we are selecting the Microsoft SQL Server
Once you are done filling the details, please click on the Create button to create a new empty table in our Informatica PowerCenter Target Designer.
From the below screenshot, you can see that we successfully created a new empty table in our Target Designer.
To add new columns, we can edit the table definition by Right-click on the table definition and selecting the Edit..option from the context menu.
Once you select the Edit.. option, a new window called Edit tables will open. Within the Table tab, we can rename the table name by clicking the Rename button. Please change as per your requirements
To alter or modify the existing table, we have to navigate to Columns Tab. Here we want to add new columns so, Please select the first button (before scissors symbol) as shown below. It opens an empty row to add Column Name, Data Type, Precision (if required), Scale (if needed).
If it is a primary key, then change the Key type to primary and checkmark the Not Null option (if your column doesn’t allow null values)
From the below screenshot, you can observe that we added six columns with three NVarchar data types and three Numeric data types. Once you added the required columns, Click OK to close the Edit tables window
From the below screenshot, see our newly created target table in Informatica. And also the table definition (Column Names and appropriate data types) inside our workspace.
NOTE: The target tables we created in Informatica PowerCenter target designer will exist only in the target designer. If you want to add this table definition to SQL Server, we have to follow the steps we specified in Create Target table using Source Definition article (Starting from Step 1 to Step 8)