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
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.
(select max(COL_ID) max_id from DATABASENAME.TGT_TABLENAME ) max_val
WHERE NOT EXISTS(
SELECT '' FROM