Is there a way to generate a surrogate key dynamically using set processing rather than record level processing? We have a large amount of records in the source which we need to generate a unique identifier in the EDW for and cursor level processing seems to be quite slow in generating surrogate keys for this volume of data.
To illustrate what we are doing, we are trying to create a system identifier address_id for each unique combination of 7 address fields from a source system.
SEL CSUM(1,(ADDR.ADDRESS_LINE1 || ADDR.ADDRESS_LINE2 || ..) ) + DT.MAX_ADDRESS_ID AS ADDRESS_ID FROM SOURCE.ADDRESS_TABLE ADDR, (SEL ZEROIFNULL(MAX(ADDRESS_ID)) MAX_ADDRESS_ID FROM TARGET.ADDRESS_TABLE) DT
WHERE (ADDR.ADDRESS_LINE1 || ADDR.ADDRESS_LINE2 || ..) NOT IN (SEL (ADDR.ADDRESS_LINE1 || ADDR.ADDRESS_LINE2 || ..) FROM TARGET.ADDRESS_TABLE) ;