I have two data pipelines in a dataflow and would like to merge them to
achieve the same thing as this sql statement:

select col1, col2, col3
from tablea
union
select col1, col2, col3
from tableb
where not exists (select 1 from tablea where tablea.pk = tableb.pk)

I can't figure how to implement the where not exists part of this. I
don't think the merge is doing the same thing as this.

Any ideas?

Thanks!

Hello jimdandy (AT) shaw (DOT) ca,

That definitely sounds like a job for the UNION ALL transform.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I don't think a UNION ALL will work, but thanks for the suggestion.
That was the first obvious thing that popped into my mind as well.

To make it clearer and easier to understand I'll give an example:

tablea.pk tablea.col1 tableb.pk tableb.col1
------------- --------------- -------------- ---------------
122 b
123 x 123 y
124 z1 124 z2
125 a

In this example, there is no row 122 in tablea and no row 125 in
tableb. Both tables do have rows 123 and 124.

This is the output I want:

122 b
123 x
124 z1
125 a

You'll notice for row 123, I want the value from tablea since it exists
in both tables.

Any ideas?

Ah OK that makes things a bit clearer.

Ok So you have two streams

Stream 1

Take everything here

Stream 2

Lookup against Stream 1 to see if I have your key in there.
Yes then ditch rows
No Then take Stream 2


"UNION ALL" these two streams together

Make sense?


--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


<jimdandy (AT) shaw (DOT) ca> wrote

Very well put Allan. So, I'll use the LOOKUP to determine if the row
needs to be be included or tossed, then UNION ALL the two streams...
will give that a try. It's hard to wrap my head around the new thinking
required in SSIS.

Keep in there. Once you "See the light" you'll be hooked.

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


<jimdandy (AT) shaw (DOT) ca> wrote

Actually, there is one more hitch. In this example, tablea is actually
a dataflow where a bunch of transforms have already been applied, so I
can't use a LOOKUP unless I persist the data to a table, etc. If I do
that, might as well just write a SPROC since it will execute
significantly faster. TableB can be an OLEDB datasource, but TableA
isn't.

Any ideas?

Thanks!

You could get inventive and use the MERGE JOIN transform.

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


<jimdandy (AT) shaw (DOT) ca> wrote