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.
1
2
3
4
|
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.
If you want to restrict which rows from the table get written into the data file, you can add a where clause:
1
2
|
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.
1
2
3
4
|
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).
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:
1
2
|
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:
1
|
REPLACE_DATA BAS_EVT_CLASS;
Resources
|
No comments:
Post a Comment