April 7, 2010 at 7:24 am
Table1:
Clo1(PK)Col2Col3
1AX
2BNull
3CZ
4DNull
Table2:
Clo1(PK)Col2Col3
1PP
2RY
5GZ
Result:
Clo1(PK)Col2Col3
1AX
2BY
3CZ
4DNull
5GZ
Conditions:
1.If a record with the same primary key is found in both the tables (table1 and table 2), then the Table1 records should be used. All the columns in the Result table will be loaded from the table1 data except the Col3, which will be loaded from the Table2.
2.If a record exists in Table2, but not in Table1, then the record should be loaded from the Table2 data. All the columns in the Result Table will be loaded from the Table2 data and the Col3 column will be left <null>.
3.If a record exists in Table1, but not in either of the Table2 data source, then the record should be loaded from Table1. All the columns in the Result table will be loaded from the Table1 data and the Col3 column will be left <null>.
Pls help me in this concern.
Thanks in advance.
April 7, 2010 at 8:57 am
ptjj (4/7/2010)
Table1:Clo1(PK)Col2Col3
1AX
2BNull
3CZ
4DNull
Table2:
Clo1(PK)Col2Col3
1PP
2RY
5GZ
Result:
Clo1(PK)Col2Col3
1AX
2BY
3CZ
4DNull
5GZ
Conditions:
1.If a record with the same primary key is found in both the tables (table1 and table 2), then the Table1 records should be used. All the columns in the Result table will be loaded from the table1 data except the Col3, which will be loaded from the Table2.
2.If a record exists in Table2, but not in Table1, then the record should be loaded from the Table2 data. All the columns in the Result Table will be loaded from the Table2 data and the Col3 column will be left <null>.
3.If a record exists in Table1, but not in either of the Table2 data source, then the record should be loaded from Table1. All the columns in the Result table will be loaded from the Table1 data and the Col3 column will be left <null>.
Pls help me in this concern.
Thanks in advance.
If I understand your question
Dataflow1. Use Table 1 as your source, lookup in Table 2. Route all records not found in table 2 to an insert statement for your result set.
Dataflow2 Use table 2 as your source and lookup in result table. Route successful lookups to update records COL3, route unsuccessful lookups to insert new records
I don't think I understand your 3rd point. I think your insert statements should be able to handle your nulls as you outline.
April 7, 2010 at 9:02 am
Hi,
Thanks for ur reply...
one more correction..
Both the sources are in the raw files(i already stored all the data in the raw files according to my requirement),so, in this case LOOKUP is not possible.
PLease suggest any other alternative.
April 7, 2010 at 9:59 am
Well you could use the Execute SQL Task to create a couple of temporary use tables and then drop them when all your data transformations are completed and still do it as I suggest.
You might also try the Import Column to load both table 1 and table 2 data sets into a single data set and then handle your logic as to which to use in your insert/update statements to your destinations. I would have to fiddle with that for a while to make sure I could get it to behave the way I wanted.
Good luck.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply