Surrogate Key Question

Database
Enthusiast

Surrogate Key Question

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.

Thanks,

Todd Thompson
2 REPLIES
Enthusiast

Re: Surrogate Key Question

You can try something like below -

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) ;
Senior Apprentice

Re: Surrogate Key Question

Hi Todd,
this is usually done using a Left Join of target/source and COALESCE(address_id, ROW_NUMBER + (SELECT COALESCE(MAX(address_id), 0) FROM target)))

Dieter