I have one table - TableA. This is source and target also. Table doesn't have any primary key. I am fetching data from TableA, then doing some calculation on some fields and updating them in same tableA. Now how can I update data when it doesn't have any primary key or composite key? Second question - If joining two columns make a record unique then how can I use it in informatica?Plz help
3
-
3First step: add a primary key. Every table that you use should have a primary key. After all: how else do you uniquely identify your individual rows, if you want to operate on them??marc_s– marc_s2012-02-25 14:38:40 +00:00Commented Feb 25, 2012 at 14:38
-
Providing an example of the record format/data would help us to understand the problem better. It's quite unusual not to have a way to identify unique records, but as long as you don't need to distinguish between rows which have the same values (for example, if your calculation is purely based on the data in the source rows, and you accept that the table will have duplicates) then you can still go ahead and update the row (using the entire row as the key value if necessary). Seems like that wouldn't usually be great design though.bob– bob2012-02-27 09:41:42 +00:00Commented Feb 27, 2012 at 9:41
-
Hi Bob, so how can I make entire record as key value?user1173496– user11734962012-02-29 05:04:24 +00:00Commented Feb 29, 2012 at 5:04
Add a comment
|
2 Answers
You can define the update statement in the target. There is that properties. Still you have to make informatica to perform an update, not insert. To do that you need to use the update strategy. I think you don't need in this solution to make any PK on that table, because you will use your own update statement, but please verify this.
To set the fields and make proper where condition for update you need to use :TU alias in the code. TU -> means the update strategy before the target.
Example:
update t_table set field1 = :TU.f1 where key_field = :TU.f5
Comments
- If you don't want (or can't) create primary key in your table in database you can just define it in informatica source
- If record unique as combination of two columns just mark both of them as primary key in informatica source
