Datamover is probably the best way to export and import data between PeopleSoft databases and the scripts are very easy to write. This wiki page provides template export and import scripts to save you having to search around for the last one you wrote!
A couple of notes:
- I normally set the log/output/input locations to C:\ drive as everyone should have one of those. You can leave the path blank and use the settings configured but this can end up putting the files in some strange places! You can also change this path to whatever you like, but don't assume too much about other people's settings. E.g. D:\ may not work as not everyone has a D:\ drive.
- You can add appropriate where clause statements after the export line.
- You can export as many tables as you like by adding additional export lines.
- In this example I've implicitly specified the table to be imported rather than using import * but you can do this as well. The latter saves a bit of typing!
- If the data already exists then you will get unique constraint errors using import. Use replace all to drop the table/indexes, recreate the table, and insert the data from the input file. Use replace_data if you just want to delete and re-insert data into the table.
Export Script
-- REF0001 DD/MM/YYYY Author-- Brief explanation of what is being exported set log C:\REF0001_EXPORT.log;set output C:\REF0001.dat; export TABLE_NAME;
Import Script
-- REF0001 DD/MM/YYYY Author-- Brief explanation of what is being imported set log C:\REF0001_IMPORT.log;set input C:\REF0001.dat; import TABLE_NAME;