Home > IT Answers > AS/400 > COPY data between to files with SQL in iSeries
BUG68
225 pts.
 COPY data between to files with SQL in iSeries
iSeries SQL, SQL, SQL Copy Record, SQL Record
hi, i need help with this: i have two files in Iseries one of them is new and other have old records and i pretend with sql copy some records. But file 1(new) and file 2(old) they don't have same collums file1 file2 fielda fielda fieldb fieldb fieldc filedd fieldd how can i make the correspond field in sql instruction? thanks

Software/Hardware used:
ASKED: January 14, 2009  2:29 PM
UPDATED: January 14, 2009  2:56 PM
RELATED QUESTIONS
Answer Wiki:
Hi, You can use CPYF with the record format field mapping options to do this :- CPYF FROMFILE(Yourlib/Oldfile) TOFILE(Yourlib/Newfile) MBROPT(*REPLACE) FMTOPT(*MAP *DROP) Regards, Martin Gilbert.
Last Wiki Answer Submitted:  January 14, 2009  2:33 pm  by  Gilly400   23,625 pts.
All Answer Wiki Contributors:  Gilly400   23,625 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Martin’s answer is excellent.
CPYF will do lots of good stuff and doesn’t need SQL.
It can do stuff like select records (where)
But if you need SQL.

Here is the trick —
Assuming that you haven’t defined column FieldC to allow null.
Typically you won’t want AS/400 columns to allow null.
Then you must provide values for all columns.
So I’ve added a value ‘ ‘ or 0 to the subselect results to be mapped into File1

If FieldC is character:

INSERT INTO File1
(fielda, fieldb, fieldc, fieldd)
SELECT Fielda, Fieldb, ‘ ‘, Fieldd
FROM File2

If FieldC is numeric

INSERT INTO File1
(fielda, fieldb, fieldc, fieldd)
SELECT Fielda, Fieldb, 0, Fieldd
FROM File2

Phil

philpl1jb
 44,190 pts.