Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using value from target table to filter values from source Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:33 PM
Points: 1, Visits: 32
Hello, I want to import all the rows from a source table where the value of a column (ID) is greater than max(ID) in the target table (thereby getting only new rows).

Lookup transforms do joins, and both tables are > 200M rows, so that seems like a waste when all I need is a where filter.

This sounds simple, but I cant figure it out using SSIS. Linked servers would solve this, but that's not an option here.

Thanks in advance for any help.
Post #1418686
Posted Monday, February 11, 2013 7:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
I would do this in two steps:

1. Run an Execute SQL Task to retrieve the max ID from the destination table and store the result in a variable.
2. Run a Data Flow Task to copy the data from the source where the ID is greater than the ID you retrieved from step 1.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1418714
Posted Tuesday, February 12, 2013 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 5,317, Visits: 12,352
opc.three (2/11/2013)
I would do this in two steps:

1. Run an Execute SQL Task to retrieve the max ID from the destination table and store the result in a variable.
2. Run a Data Flow Task to copy the data from the source where the ID is greater than the ID you retrieved from step 1.


+1 exactly how I would do it.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1418803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse