how to select dynamic date from table1 to use in select table2

Question

Post reply

how to select dynamic date from table1 to use in select table2

adamrc

SSC Rookie

Points: 35
More actions
November 22, 2015 at 11:38 am

#313254

scenario:
remote_table2 extracts from odbc connection (cloud)
local_table1 is local that is always truncated and loaded with new data prior to table2.
we want to compare data between local_table1 and local_table2. local_table1 can have 5 days or 30 day worth of data at any given time.
how do i do this in 1 step? or best method suggestions?
data flow task:
select *
from remote_table2 t2
where t2.date_last_modified >= (select min(date) from local_table1)
(then store to local_table2)
I'm not specific about the datatype of date here because I'm curious how to do this in SSIS.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

https://msdn.microsoft.com/en-us/library/ms162843(v=sql.120).aspx

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 2

Select * from Table1

Except

Select * from Table2

It will show all mismatch records between table1 and table2

adamrc SSC Rookie Points: 35 More actions · Answer 3

Thanks SoHelpMeCodd and johnwalker10. Now I know that there is such utility and syntax :).

Although, I'm curious how to pass the min(date) when my current connection manager in that task via sql command is ODBC to remote table. Note that min(date) is requesting data from local db.

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 4

If you wish a question to be focused on Integration Services, please post your question within the Integration Services forum (http://www.sqlservercentral.com/Forums/Forum364-1.aspx).

However, a quick search led me to http://blogs.msdn.com/b/jorgepc/archive/2010/12/09/synchronize-two-tables-using-sql-server-integration-services-ssis-part-ii-of-ii.aspx and http://blogs.msdn.com/b/jorgepc/archive/2010/12/07/synchronize-two-tables-using-sql-server-integration-services-ssis-part-i-of-ii.aspx, which appear to broach the same methods we had offered, adds to them, and appears to answer most of your remaining questions.

As far as whether harnessing an OLE DB or an ODBC connection poses a problem (I don't think it does), I believe members of above forum would be able to offer you some further SSIS-specific guidance. As far as a single step is concerned, you may wish to post in above forum why it is imperative to accomplish what you desire within one step.

adamrc SSC Rookie Points: 35 More actions · Answer 5

Appreciate the links! Wealth of ideas I can use for other projects.