Surrogate Key: Part 2

Well, there is another way to do it…
After computing MAX of the column value and use ROW_NUMBER() function. Please note that you can use CSUM(1,1) as well in the same logic.

INSERT INTO DATABASENAME.TGT_TABLENAME
SELECT
MAX_ID + ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY 1) as COL_ID --Partition by is optional. If your values won't repeat, you don't have to use it.

,COLUMN2
,USER
,CURRENT_DATE
FROM
DATABASEANAME.WRK_TABLENAME wrk,
(select max(COL_ID) max_id  from DATABASENAME.TGT_TABLENAME ) max_val
WHERE NOT EXISTS(
SELECT '' FROM
DATABASENAME.TGT_TABLENAME  lkp
WHERE
LKP.COLUMN2= WRK.COLUMN2
);

~Meena

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

~Meena