Data Mover Import Export

A common development task is to move data between two different PeopleSoft environments.  You can use App Designer to move code, table structures, and pages, but data is a different story.  Data Mover is your tool for moving your data.
My biggest challenge is that I don’t use it enough to have the syntax memorized.  This post is a list of common tasks with the syntax to make it easier to remember…

Copying Data Out

The export command copies data out of the database into a DAT file.  You should set the Log parameter to save the log to a particular place.

SET LOG C:\Temp\export.log;
SET OUTPUT C:\Temp\data.dat;

EXPORT PS_BAS_EVT_CLASS;

You can use the record name or the table name.  So, here’s the same thing with the record name instead of the table name.

EXPORT BAS_EVT_CLASS;

If you want to restrict which rows from the table get written into the data file, you can add a where clause:

EXPORT BAS_EVT_CLASS
WHERE EVENT_CLASS = 'CVG';

Copying Data In

The import command is the way to copy the data back into the system.  You will want to set the Log parameter and you have to set the Input parameter.

SET LOG C:\Temp\import.log;
SET INPUT C:\Temp\data.dat;

IMPORT BAS_EVT_CLASS;

If you want to limit which rows you want to import, you can use a Where clause.  For me, it wouldn’t work unless I used the parameter (:1).

IMPORT BAS_EVT_CLASS WHERE EVENT_CLASS = :1;CHAR,CVG;

Now, if the row already exists, it will ignore it.  There is a “Set Ignore_Dups” option, but I didn’t have to use it.  It just ignored the row that already existed.  To get it to update the row, I used the “Set Update_Update” option:

SET UPDATE_DUPS;
IMPORT BAS_EVT_CLASS WHERE EVENT_CLASS = :1;CHAR,CVG;

These commands so far work with only the rows that are in the data file.  If the database has extra rows that you don’t want, you need to use Replace_Data.  You could truncate the table first, but the Replace_Data command is designed for just this:

REPLACE_DATA BAS_EVT_CLASS;