All Forums Database
thompsonhab 13 posts Joined 09/12
04 Mar 2013
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

KS42982 137 posts Joined 12/12
04 Mar 2013

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

dnoeth 4628 posts Joined 11/04
04 Mar 2013

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

Dieter

You must sign in to leave a comment.