Putting together a few steps to generate surrogate key:
Most of you might've dealt with it already. But, sending it as it might be a quick reference incase of future use
Generating it as such isn’t a big deal, it might get a little tricky when you are trying to insert new values in continuation of already existing surrogate key.
In the Bteq script, you may use a logic that exports current value of the surrogate key column into a data file:
.EXPORT DATA FILE <$DIR/filename>
SELECT MAX(SURR_KEY_COLUMN) (TITLE '')
/****Read in MAXID variable*****/
.IMPORT DATA FILE <$DIR/filename>
USING (MAXID INT)
The above steps will populate MAXID with maximum value of surrogate key.
Now, this variable can be used along with CSUM function to start from where we stopped :)
INSERT INTO TGTDB.TGTTBL
:MAXID + CSUM(1,1)
LEFT OUTER JOIN TGTDB.TGTTBL T2
ON T1.COLUMN2 = T2.COLUMN2
T2.COLUMN2 IS NULL
The above is a typical example in loading target table(such as a lookup table) from staging table; with surrogate keys for the new values of COLUMN2
Thanks a lot for this post, since it is informative and comprehensive.ReplyDelete
Keep Rocking !!!!!!!
thank you dixit :)ReplyDelete
I will try to post more often
its really beautiful explanation.Can i expect more concepts from you.