Profile Multi-Set Select Statements

At times we need to select from two or more sources and stage the selected source.  As an example, you may want to collect all historical records from an archive on the initial load, and collect only new records from an online source for all subsequent executions.

 

There are a number of ways to accomplish this.  The challenge comes when trying to get the SQLNCLI to detect the metadata correctly.  We have found that using If Else logic is the most problematic.

 

So, avoid doing this:

If [MyStat] = [InitialStatValue] 
Begin 
  Select from Table1 
End 
Else 
Begin 
  Select … from Table2 
End

 


Instead, use set operations like this.

Select … from Table1 Where [MyStat] = [InitialStatValue] 
Union 
Select … from Table2 Where [MyStat] <> [InitialStatValue]

 

While both of these queries can return the exact same results, you will have a much better chance of correctly detecting the fields and data types using the latter option.  Full joins along with field selection based on the current statistic value can work as well, but would require more scripting.

 

The key to making this work is the creation of a statistic (typically in PSA) with an initial value that will never appear again.  Trigger on the initial value to do you initial load from an archive or other initial source.  All subsequent loads will be automatically redirected to the online source.  This simplifies your ETL by preventing the need to have duplicate stage & tables that will need to be combined through some type of transformation later in the data flow.

What We Do

We provide SMBs with custom data analytics services.

Built to Spec
We design custom solutions.

Low Cost per User
Empower as many report users as you like.

You Own It
Everything from the data solution to reports is yours to keep.

Month-to-Month
No upfront costs and no long-term commitment.

Available 24/7
We monitor and maintain everything 7 days a week.

Support
Unlimited access to a team of data experts.

Find out how 
our clients
are getting

10x ROIwithin 6 months

CLICK HERE

Stay Connected With Us

Join our monthly newsletter to receive LeapFrogBI’s latest insights and articles on automated, customized reporting.

LET’S TALK

Have any questions? Reach out to us, we would be happy to answer.

';