Archives of the TeradataForum
Message Posted: Thu, 13 Mar 2008 @ 11:33:05 GMT
Yes, indeed, I didn't need a Cursor after all, as *Dieter *corrected determined that all the logic was contained within the SQL.
INSERT INTO DEVUK_W.FINAL_SET * SELECT RN1.OLD_ACCT_ID , RN1.NEW_ACCT_ID FROM DEVUK_X.RENUM_CAND RN1 LEFT JOIN DEVUK_V.UK_ACCOUNT_RENUM_V01 RS1 ON RN1.NEW_ACCT_ID = RS1.ACCOUNT_NUMBER_OLD WHERE RS1.ACCOUNT_NUMBER_OLD IS NULL* ;INSERT INTO DEVUK_X.TEMP_SET * SELECT RN1.OLD_ACCT_ID , RS1.ACCOUNT_NUMBER_NEW FROM DEVUK_X.RENUM_CAND RN1 LEFT JOIN DEVUK_V.UK_ACCOUNT_RENUM_V01 RS1 ON RN1.NEW_ACCT_ID = RS1.ACCOUNT_NUMBER_OLD WHERE RS1.ACCOUNT_NUMBER_OLD IS NOT NULL* AND RS1.ACCOUNT_NUMBER_NEW <> RS1.ACCOUNT_NUMBER_OLD;
The moral of the story here is, when dealing with Stored Procedures, to consider whether Inserts (or Updates or Deletes) are required from the result of a Select. If so, then try to avoid doing it with a Cursor at all cost !
I do feel, however, that there's something missing from Teradata at the moment, at least in the arena of Stored Procedures. I tried the Inserts - using a Cursor - into a Volatile table and indeed it was excruciatingly slow. It seems to me that Teradata needs another version of a Temporary table that facilitates very fast consecutive Inserts, perhaps based upon zero logging or something.
Anyway, thanks again *Dieter*, I owe you yet another pint of beer.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|