Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to implementt his query in Condtional Spilit for Table except with merge join

Answered how to implementt his query in Condtional Spilit for Table except with merge join

  • Monday, December 31, 2012 5:58 PM
     
     
    Sign In to Vote
    0

    Hi all!

    Happy New Year!!!!

    here looking for insert and update with difference in two tables.  Depends upon on the value of the source and destination table, update statement should execute.

    For example 

    [ Dest Table logistic in ('SB','TR') and Src Table b.logistic in ('SC','','RC','RB') ] OR [ Dest Table logistic in ('SB','TR') and Src Table b.logistic in ('SC','','RC','RB')  ] then update Src valueb.logistic otherwise retain dest value.

    Sample Script

    update Dest_tab Logistic= case
                   when a.logistic in ('SB','TR') and b.logistic in ('SC','','RC','RB') then b.logistic
                   when isnull(a.logistic,'')=''  and b.logistic in ('RB','SC') then b.logistic
                  else a.logistic end

    Location= case                                  
                    when a.logistic in ('SB','TR') and b.logistic in ('SC','','RC','RB') then b.Location                                          
                    when isnull(a.logistic,'')=''  and b.logistic in ('RB','SC') then b.Location  else a.Location end,              
    SentBy =  case                                  
                 when a.logistic in ('SB','TR') and b.logistic in ('SC','','RC','RB') then b.SentBy                                          
                    when isnull(a.logistic,'')=''  and b.logistic in ('RB','SC') then b.SentBy                                  
               else  a.sentby end,                                    
    SentTo = case                                   
                    when a.logistic in ('SB','TR') and b.logistic in ('SC','','RC','RB') then b.SentTo                                          
                    when isnull(a.logistic,'')=''  and b.logistic in ('RB','SC') then b.SentTo                                  
                else  a.SentTo end, from Dest_tab a join Src_tab b a.pk_id=b.pk_id

    ......

    my Data flow Task is

    1.two OLE DB Sources

    2.Mege Join (PK_ID -join key)

    3.Conditional spilit

       a.Insert case - isnull(DstPK_ID)

       b.Update case -?

    4.OLE DB Destination for insert and OLE DB Command for update.

    now need guide for Update case expression. and any good method to implement this..

    Thanks regards with

    T KUMAR

     

All Replies

  • Monday, December 31, 2012 6:12 PM
     
     Answered
    Sign In to Vote
    0

    I think you can use look up task that can perform insert as well as update. The insert goes into ole db destination and update requires ole db command task for updating the table.

    Thanks,

    hsbal

    • Marked As Answer by Kumar A T Monday, December 31, 2012 6:24 PM
    •  
     
  • Monday, December 31, 2012 6:24 PM
     
     
    Sign In to Vote
    0

    but how to i implement the update case. i already done some table difference projects. but here i confused with conditional spilit update case expression.

    thanks

     
  • Monday, December 31, 2012 7:15 PM
     
     Answered Has Code
    Sign In to Vote
    0
     
    update table
SET column1=?,
    column2=?
where column3=?

    This syntax is used for update statement in ole db command task.

    Thanks,

    hsbal

    • Marked As Answer by Kumar A T Monday, December 31, 2012 7:29 PM
    •  
     
  • Monday, December 31, 2012 7:29 PM
     
     
    Sign In to Vote
    0
     
  • Monday, December 31, 2012 7:57 PM
     
     Answered
    Sign In to Vote
    0

    Please refer below link that explains look up task configuration:

    http://www.rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.html

    Thanks,

    hsbal

    • Marked As Answer by Kumar A T Monday, December 31, 2012 8:12 PM
    •  
     
  • Monday, December 31, 2012 8:14 PM
     
     
    Sign In to Vote
    0

    Can u help in Conditional Spilit. Bcse i don not use Stored Procedures .

    Sample

     
  • Monday, December 31, 2012 8:38 PM
     
     
    Sign In to Vote
    0

    I think after the merge join, you can use look up task. Therefore, your conditional split gets removed. Now, you can put the update statement logic in sp and call that sp in ole db command (explained in link). My understanding is that it would provide you the results.

    Thanks,

    hsbal