Re: SQL Insert question

[Jason Olson <josys36@xxxxxxxxx>]

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>