Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Updating source to indicate successful insert in DM
Go
New
Search
Notify
Tools
Reply
  
Updating source to indicate successful insert in DM
 Login/Join
 
Platinum Member
posted
Ok, here's my scenario that I'm unclear of how to solve:

Table A has n number of transactions that I need to load all of into my target B. This file has a field on it that needs to be updated to a 'Y' for each record successfully added to B.

Table B has a surrogate key that assigns a one-up number for each individual transaction that gets inserted into it, allowing duplicates.

Here's what I dont know how to do; after the record gets added to the file, how do I go back to the original source and indicate that the record should be updated with a 'Y?' I was thinking about making a temp table that just stored all of the records that were successfully processed by DM, but if I do that, I have no way of ensuring the ON MATCH didnt fail for some reason.

Any suggestions?


WebFOCUS: 7702
O/S : Windows
Data Migrator: 7702
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Reply With QuoteReport This Post
Member
posted Hide Post
I am restating your issue to make sure I understand.
quote:
When the record from table A is successfully put into table B update the table A record with a "Y"

Assuming you have access to servers/databases/tables the easiest way to do this is straight SQL. Actually, as I think about it I wonder why you are using DM for this. So before I blather on about my answer, and look more ignorant than I am in things DM, please explain to me the architecture of your system and why DM is used versus straight SQL to accomplish moving data from one table to another.


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
If there are key fields in common between table A and table B, then just do another DM data flow with table A inner joined to table B as the source and just table A as the target. Where you find records in both table A and table B, then do an update to table A to set the updated indicator to a "Y".


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Related to my last post, I am curious why the whole thing wouldn't be done in SQL in the first place? Is it the proverbial "When you have a hammer ..."?

It just occurred to me that perhaps DM would allow single insert failures while allowing the rest to succeed. Is that where my ignorance of DM is stopping me?

BTW: Jewssica's answer is where I was going in my SQL only thread.


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Updating source to indicate successful insert in DM

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.