Archives of the TeradataForum

Message Posted: Fri, 16 Sep 2005 @ 14:11:41 GMT

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

I have a similar process that compares two very large tables to see what changed. In my example, I am updating one table with changes from a second table, and inserting the updated stuff into a third empty table.

I've bastardized it below to give you an idea of the SQL that I found to be very efficient. Low spool use, and good use of indexes.

Hopefully this makes sense.

     replace macro db.MA_sample_LD as
     (INSERT into db.sample
     -- This will insert rows from Table A that are different in Table B
     (Expiring them, but keeping for history).
     Select
     A.column1                  ,
     A.column2,
     . . .
      from db.TABLE_A A,
           db.TABLE_B B
      where
      A.index_column1                 =B.index_column1
     AND
      A.index_column2                 =B.index_column2
     . . .
     AND (
     A.column3 <> B.column3, etc. may need to handle nulls also if columns
     are nullable)
     ;INSERT into woodrow.sample
     -- This will insert rows from Table B that are different in Table A.
     Select
     (this would be the same as above with table reversed)

     ;INSERT into db.sample
     -- This will insert rows from Table B that are not in Table A.
     Select
     B.column1,
     ...
      from db.table_B B where not exists (select 1 from db.table_A A where
     A.index_column1                 =B.index_column1, etc.)
     ;INSERT into db.sample
     -- This will insert rows from A that are not in B
     Select
     (Same as above with tables reversed)
     ;

(There is another INSERT to capture the rows that didn't change)

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