February 13, 2008 at 10:26 am
Hello ,
I have a table A that has colums
ID Name Address SSN ZIp etc
Table B with the same colums
ID Name Address SSN
I do a LEFT JOIN on the id of table A & table B
select a.address, ad.ssn from A
left join A on a.id = b.id
I use the above query in an SSIS package
Source--->Destination
where the destination has say 1000 rows. I add the address and ssn from table A onto B where there is a join.
HOWEVER,
After running the package it inserts from the 1001 row of table B..i.e. after all rows..and has a null for id, name etc...
The package also seems to pause midway..
Can anyone please let me know what the problem is??? (It may be kind of obvious but do let me know!)
Thanks,
February 13, 2008 at 11:04 am
I'm not sure I fully understand your problem. Are you expecting that all of the records in the destination table will be overwritten? If so, you need to include some logic to do this - by default, it will simply append rows to the table if data already exists in the destination.
If you are finding some unexpected NULLs in your JOIN, this is caused - at least in part - from your LEFT JOIN. Doing a LEFT JOIN will include all rows from the left side of your join and will marry those with any matching rows on the right. If you only want to include rows that are matching, use an INNER JOIN instead.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 13, 2008 at 11:24 am
No, not overwritten. Just wanto add the address and SSN column from table A onto table B..where there is a join on the id's of the two tables. So for every id on table b that is matched i wanto add the address and ssn
Additionally the package does not get completed. It copies 9,452 rows(out of 2 million) and like just pauses(shows a yellow).
Thanks,
J D
February 13, 2008 at 11:55 am
would an update table statement do the trick, however is it possible to update a column of one table to a column from another??(which is referenced in the join)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply