How to implement join

Rajendra_89 · Post by **Rajendra_89** » Sat Jul 13, 2013 8:39 am

Hi Craig,

I have gone through the answers from forums other questions for this topic but yet I am not sure ...

I want to join a sql server table and text file on the basis of Varchar column and then two sqo server tables

But as the join stage is not supported in Server edition I am not able to figure
how this join can be achieved

Two sql server tables I can join in the ODBC stage..

But how to join a text file with Sql server table by matching their varchar column?

Thanks in Advance!

chulett · Post by **chulett** » Sat Jul 13, 2013 9:11 am

That would be the Merge stage, not to be confused with the Parallel stage off the same name.

Rajendra_89 · Post by **Rajendra_89** » Sat Jul 13, 2013 9:25 am

Correct me if I am wrong Craig

Merge stage is used to join only two sequential file

I want to join one sequential file with one Sql server table which I am importing in ODBD stage..

How can I join this file with SQL server table?

chulett · Post by **chulett** » Sat Jul 13, 2013 10:17 am

Either import the file into your database and join them there or export the table to a file and use the Merge stage. From what I recall,those are your two primary choices in a Server job.

Depending on the type of kind of join, you may be able to put the file into a hashed file and use a lookup to perform the join.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 13, 2013 3:35 pm

Read the text file and perform lookups against the table, either directly or a copy pre-loaded into a hashed file.

Rajendra_89 · Post by **Rajendra_89** » Mon Jul 15, 2013 8:48 am

Thanks Chulett and Ray

I am not clear with the concept of Hash files And as Server jobs dont have a Look up Stage how can we perform look up on hash files?

I dont have any idea of implementing the Hash files

The more am reading on it more getting confused

Please guide me on this...

ray.wurlod · Post by **ray.wurlod** » Mon Jul 15, 2013 4:55 pm

In server jobs lookups are performed with the Transformer stage.

The server Transformer stage takes one stream input and zero or more reference inputs (these are painted with short dashed lines). Reference inputs provide the lookup functionality.

Hashed files (note, not "hash" files) are implemented using Hashed File stage. You can get by using the defaults in this stage.

Rajendra_89 · Post by **Rajendra_89** » Tue Jul 16, 2013 12:55 am

I am working on this ...
Lets see If I succeed

rameshrr3 · Post by **rameshrr3** » Fri Jul 19, 2013 1:16 pm

Im curious - if you are using an ODBC stage , and network latency is low enough , you may be able to use the oDBC stage as a reference link input , with its reference returns multi rows property set in the transformer ( identity stage) . The file will be stream input link .

ray.wurlod · Post by **ray.wurlod** » Fri Jul 19, 2013 4:07 pm

Yes, that's precisely what I suggested. If network latency is an issue, pre-load the table (only the rows and columns you actually need) into a hashed file.