2

I am using Teradata and my query is 

SELECT deptno, name, salary
FROM personnel.employee;

Above table automatically gets updated everyday e.g. new rows added/deleted, existing values gets updated. There are no date/time field.

Is there a way I can only get the changes and not all the records? Also is there a way to know which of the record are new vs. which are updated? Happy to run multiple query as the file size will be small. Does Teradata have internal table where it keeps record of updates/deletes? do I need special permission to access such table? thanks.

wailer
  • 511
  • 7
  • 22
  • I would think they would want to maintain history on employee salary. If they are not already, I would suggest they add new records to the employee table with a new time stamp/effective date/time and leave the old records in the table. – wavery May 08 '19 at 04:24
  • Unfortunately I don't have that level of access to change tables etc. I have to download this large file everyday.. I was wondering if there was any built in date function in teradata? – wailer May 08 '19 at 05:41
  • I don't know that Teradata would maintain history by record in a table. I think it can store modified date of a table, but that probably wouldn't help. You could either have your Teradata DBAs/ETL people begin storing history, or you could possibly create it yourself by saving your daily query results and then comparing each day to the day before. But you would need to pull in some sort of key field. Name or Deptno probably wouldn't work. – wavery May 09 '19 at 00:33

1 Answers1

1

In the given scenario itself you don'T have a chance to see the diff. But some ideas to improve the table / situation:

  • You can change the Table to a TEMPORAL table. Then Teradata will add invisible columns with timestamps that are maintained automatically. With this you can access these temporal columns to get the diff. In your case it looks like a transactional temporal table. (This is what I would prefer)
  • You can create you own table holding the latest status and each join with this table to get the delta.
hhoeck
  • 361
  • 2
  • 3