Archives of the TeradataForum

Message Posted: Wed, 04 Aug 1999 @ 17:36:10 GMT

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

Bharat

You might remember this. To keep track of the number of rows added into a table we used a script like the one attached. There were more complicated scripts that did the same thing used to track ActivityCount.

Script is from TeraScript utility

     /*--- This script manages to merge the Person_Demographics table   ---*/
     /*    from the Security profile to production IJDW        */
     /*    Must be run from staging area where Person_Demographics table   */
     /*    to be merged is at.                                 */
     /*                                                        */
     /*    Steps                                               */
     /*    1. Create a work table RIDupes to put temp list     */
     /*       of Person_ID's that are duplicate                */
     /*    2. Load table with dup Person_ID's using equi-join  */
     /*    3. Count number of rows in Stage, Production and    */
     /*       dupes (Audit)                                    */
     /*    4. Delete rows from staged using dupe temp table    */
     /*       in equi-join, better than sub-query and IN clause*/
     /*    5. Insert-Select Person_Demographics table into Production      */
     /*       use Counts for audit control again               */


     /*--- Create temp */
     Drop table RIDupes;


     Create Table RIDupes (
          "Person_ID"               CHAR(9)

                          );


     /*--- Load dupes  */
     Insert into RIDupes
        Select
           A.Person_ID
           From IJDW.Person_Demographics A, %3STAGEA%.Person_Demographics B
          Where A.Person_ID =      B.Person_ID
     ;


     /*--- Counting area  */
     Drop table RICounts;
     Create table RICounts (
         OrderIs Integer,
         Stage   Char(15),
         Status  Char(10),
         CountIs Decimal(10,0)
                            );


     /*--- Audit counts prior */
     Insert into RICounts
        Select
           1,
           'IJDW',
           'Prior',
           Count(*)
         From IJDW.Person_Demographics;

     Insert into RICounts
         Select
            2,
            '%3STAGEA%',
            'Prior',
            Count(*)
           From Person_Demographics;

     Insert into RICounts
         Select
            3,
            'Dupes',
            'Prior',
            Count(*)
           From RIDupes;


     /*--- Audit report   */
     Select *
          From RICounts
      Order By 1;


     /*********************/
     /** DELETE  DELETE ***/
     /*********************/

     Delete Person_Demographics
           Where %3STAGEA%.Person_Demographics.Person_ID =      RIDupes.Person_ID
     ;


     /*--- Audit counts DELETE */
     Insert into RICounts
        Select
           4,
           '----------',
           '----------',
           Null
         From RIDupes;


     Insert into RICounts
         Select
            5,
            '%3STAGEA%',
            'Delete',
            Count(*)
           From %3STAGEA%.Person_Demographics;


     /*--- Audit report AFTER   */
     Select *
          From RICounts
      Order By 1;


     /*******************/
     /*** MERGE  MERGE **/
     /*******************/

     Insert into IJDW.Person_Demographics
        Select *
           From %3STAGEA%.Person_Demographics;


     /*--- Audit counts AFTER */
     Insert into RICounts
        Select
          6,
           '----------',
           '----------',
           Null
         From RIDupes;


     Insert into RICounts
         Select
            7,
            'IJDW',
            'After',
            Count(*)
           From IJDW.Person_Demographics;


     /*--- Audit report AFTER   */

     /*    County: %4COUNTYA%    */
     Select
         '%4COUNTYA%',
         Stage,
         Status,
         CountIs
           From RICounts
      Order By OrderIs;

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