Home > SSIS, TSQL > HASHBYTES() for lookup

HASHBYTES() for lookup

We have many options to lookup target Junk DIM to retrieve the SID while in process the Datamart FACT tables.
I would like to do this process using HASHBYTES() as

-- Source table data with prepared HASHBYTES 
        CITY + StateProvince AS [ForHash],
        hashbytes('SHA1', CITY + StateProvince) AS [SourceHashBytes]
FROM    SalesLT.Address
-- Target table data with prepared HASHBYTES
        hashbytes('SHA1', CITY + [State]) AS [TargetHashBytes]
FROM    BITEST.dbo.TargetJunkDim
-- Lookup to Target JunkDIM to retrieve the SID 
FROM    SalesLT.Address AS [Source]
        LEFT JOIN BITEST.dbo.TargetJunkDim AS [Target] ON hashbytes('SHA1', [Source].CITY + [Source].[StateProvince]) = hashbytes('SHA1', [Target].CITY + [Target].[State])

Points to consider
1. Haven’t discussed the concept of Junk DIM here as HASHBYTES() is the main concern this post besides writing lookup script by comparing individual columns using either at ETL LOOKUP or SQL LEFT JOIN
2. Haven’t discussed the Junk DIM concept here but the above scenario is well processed to accommodate the logical flow of Junk DIM
3. Have used SQL LEFT JOIN as it is equality concept of ETL Lookup process

Categories: SSIS, TSQL Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: