February 16, 2011 at 1:25 am
Hi,
I have two tables.
TABLE A : 1 Column AID : values 1 2 3
TABLE B : 1 Column BID : values 3 4 5
If I consider A as source table and look up on table B
Then Matched o/p = 3 and UnMatched o/p = 1, 2
I want something like it should populate 4, 5 from the lookup (Rows from Lookup not matched by the source).
I know I can acheive this by just reversing the scenario (Table B as source and Lookup table A)
But for 1 crore records it becomes much slow(hectic)
In sql 2008 we have MERGE statement, in this we have if matched, if not matched and if not matched by the source. (some thing like this in lookup).
If the above is not possible suggest a suitable method to acheive the desired purpose.
February 16, 2011 at 2:25 am
You have given your suggested input data, but the desired output is unclear to me.
Are you saying that you want table A to contain all of the values in table B after the package has run?
TABLE A : 1 Column AID : values 1 2 3 4 5
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2011 at 2:29 am
I donot want table A to contain all of the values in table B after the package has run.
If I consider A as source table and look up on table B
Then Matched o/p = 3 and UnMatched o/p = 1, 2
I want 4, 5 as output..
February 16, 2011 at 2:33 am
You have merely repeated your first question. Saying that you want something 'as output' does not mean anything to me. Are you trying to populate a lookup table with missing values before doing the lookup?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2011 at 3:09 am
The way you want it, TableB is the source and TableA is the lookup table. Period.
You can try to implement it with TSQL using WHEN NOT EXISTS to speed up performance.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 16, 2011 at 3:09 am
EDIT: unfortanately posted the same message twice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2011 at 12:32 am
Yes you can use table2 has source table 1 is look up table. so that you can get the out put of 4,5 in the non matching output.
February 20, 2011 at 11:36 pm
pmm.sudarsan (2/20/2011)
Yes you can use table2 has source table 1 is look up table. so that you can get the out put of 4,5 in the non matching output.
Glad you agree with us, one month after date...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply