Re: SQL Insert question

In this example I'm showing what you would do if you wanted to do something similar to rpg record level access.

First you would need two data structures.

dcl-ds Test_Table_One extname('TST01T') qualified alias;
end-ds;

dcl-ds Test_Table_Two extname('TST02T') qualified alias;
end-ds;

These are both qualified and I use alias because I like to use the longer field names. In fact these days I ignore the shorter names completely. ( too many years now of Python )

Then you would need to read a record from the first table,

 exec sql
  declare c1 cursor for
  select * from TST01T;

 exec sql
  open c1;

 exec sql
  fetch next from c1 into :Test_Table_One;

In this case I'm only reading one record. You would need to put the fetch next into a loop checking the field SQLSTATE to see if you have reached the end of your table.

 eval-corr Test_Table_Two = Test_Table_One;

 exec sql
  insert into TST02T values(:Test_Table_Two);

The eval-corr statement will match fields in Test_Table_Two to what is in Test_Table_One. Test_Table_Two can have extra fields.

You would then set any other fields in Test_Table_Two to what you like.

Then you run the insert statement.


On 5/9/23 15:51, Greg Wilburn wrote:

I have two files/tables that are nearly identical. They are:

1. Part Master file
2. Part Master Audit file

The Audit file contains 5 additional fields that are not found in the Part Master. All of the other fields in the file are identical (mirror images of each other in name and attributes), and are in the same order EXCEPT for the last ten fields in the Audit file. The Audit file contains the 5 additional fields inserted just before the last 5 matching fields.

With RPG, I could simply chain (or read) the Part Master record, eval the 5 additional field values, and then just write the Part Master Audit record.
I'm looking for an "elegant" way to do the same thing with SQL without using native access. This will be running in a CGI job.

Any ideas?
TIA
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>