Surrogate key generation in Teradata

Surrogate key generation

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 '')
FROM TGTDB.TGTTBL;

.EXPORT RESET

/****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
(SURR_KEY_COLUMN
,COLUMN2
)
SELECT
:MAXID + CSUM(1,1)
,T1.COLUMN2
FROM
STGDB.STGTABLE T1
LEFT OUTER JOIN TGTDB.TGTTBL T2
ON T1.COLUMN2 = T2.COLUMN2
WHERE
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