Archives of the TeradataForum

Message Posted: Thu, 13 Mar 2008 @ 11:33:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

Dave Clough
Database Designer
Express ICS

www.tnt.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>