Determining Update or Insert New

[asitagrawal]

Hi,

My targt table has 2 fields:
CREATED_EW_DTTM and LASTUPD_EW_DTTM.

Now, I am using 'Update Existing or Insert New' action for writing to this table.

So, my aim is:
If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp.
Else if it is just an update then the CREATED_EW_DTTM shud remain the same and the LASTUPD_EW_DTTM shud be DSJobStartTimestamp.

My strategy is like this:

Before writing to the table, I shud do a reference lookup to determin what will be nature of the row (by suing DSLikkn.NOTFOUND etc.) or Use some Hashfile.

Is this the only way or there is some other way to determine the action being new or update??

[kduke]

Not really. You have to determine which is new in the job and then insert else update. These 2 need to be 2 different links.

[dannyna]

Well, this can be handled also by using DB constraints, means update the table key else insert the row.
But I recomend to use your strategy for perfomane reasons.

Danny

My targt table has 2 fields:
CREATED_EW_DTTM and LASTUPD_EW_DTTM.

Now, I am using 'Update Existing or Insert New' action for writing to this table.

So, my aim is:
If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp.
Else if it is just an update then the CREATED_EW_DTTM shud remain the same and the LASTUPD_EW_DTTM shud be DSJobStartTimestamp.

My strategy is like this:

Before writing to the table, I shud do a reference lookup to determin what will be nature of the row (by suing DSLikkn.NOTFOUND etc.) or Use some Hashfile.

Is this the only way or there is some other way to determine the action being new or update??[/quote]

[asitagrawal]

Not really. You have to determine which is new in the job and then insert else update. These 2 need to be 2 different links.

Plz tell me how to determine in the job itself whether the row is new or existing one?

[jdulaney]

Not really. You have to determine which is new in the job and then insert else update. These 2 need to be 2 different links.

From the column names, it looks like you are using PeopleSoft 8.9 tables. If you use the DRS, you do not need to determine whether this is an update or a new row. You can choose "update or insert" or "insert or update" and the DRS will handle it for you. Look at any existing job to see how this is handled.

[ray.wurlod]

Perform a lookup against the target table. If the row exists it's an update; if the row does not exist it's an insert. You may find it more efficient to perform the lookup against a hashed file containing the keys from the target table.