Using an SQL subselect to insert records into a table: An example

The following bit of code should be reasonably self-explanatory but I was a little surprised to discover that there are no examples of doing this (for IBM i folks, at least) on the interwebnet.

So now there’s one.

insert into ignoreiln                                               
  with
    slsrpt as (select * from ignoreiln where mtyp = 'SLSRPT'), 
    invrpt as (select * from ignoreiln where mtyp = 'INVRPT') 
  select a.mdir, 'INVRPT', a.siln, a.diln, a.riln, 
         a.dept, a.brnd, 'Auto Created' 
  from slsrpt a    
  left outer join invrpt b on b.mdir = a.mdir 
       and b.siln = a.siln and b.diln = a.diln 
       and b.riln = a.riln and b.dept = a.dept 
       and b.brnd = a.brnd 
  where b.siln is null

If you want to know more, I would recommend starting with the IBM i Information Centre.