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