Data Conversion Scripts

This wiki page is provides information about how to write data conversion scripts. While this isn't specifically related to PeopleSoft, most of my experience with converting large amounts of data has been with PeopleSoft and the Oracle DBMS so this knowledge is somewhat product specific, however much of it can be applied to any environment.

Basic structure of a conversion script

  • Truncate target table(s). This is only necessary if you are inserting new data. It is always a good idea to err on the side of deleting and replacing the data. The last thing you want is data integrity issues. Note that it is better to do a truncate than a delete here.
  • Drop the index(es) if you are inserting data. This speeds up the insert as the database does not have to update all indexes as it is inserting rows into the table(s).
  • Perform the Insert or Update
  • Recreate the indexes. You should always drop and recreate indexes if you are making large changes to data. This will ensure that the database optimiser uses the appropriate indexes.
  • Update table statistics (table analysis). This ensures that the database has a good understanding of the contents of your table(s) after inserts and updates and uses the most efficient execution plan.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License