Guru: Continue A Sequence When Inserting

January 15, 2018 Ted Holt

Carlos writes, “Hey, Ted! I have a question regarding inserting rows into a file that is keyed on a sequence number. I need to insert more rows, and I need the new rows to have the next available sequence numbers. Can I perform this task with an SQL INSERT statement, or do I have to use record-level access?”

If the sequence number were defined as an identity column, Carlos would have no problem. Unfortunately, the sequence number column is a simple numeric field. Fortunately, I was able to give Carlos two solutions. It would not surprise me if you can suggest others.

Let’s set up the problem. The table (physical file) into which Carlos wants to insert the new rows — let’s call it FILEA — looks something like this:

The table with the new data — FILEB — looks like this:

Carlos wanted to insert three new rows beginning with sequence number 6. The easy way to do this is to use a function I wrote a long time ago — the INCR function. It’s a simple RPG routine that returns an integer value that is one greater than the previous value.

insert into filea
   select incr(5), field3
     from fileb
    order by datab

Now FILEA looks like this:

If you don’t want to use my function, you can use IBM’s code instead. Create a sequence. I’ll unimaginatively call it SEQ1.

create or replace sequence mylib.seq1
    as dec (3) start with 6

Use the NEXT VALUE expression to retrieve the next sequence number from SEQ1.

insert into filea
   select next value for seq1, datab
     from fileb
    order by datab

If it were not for one little-bitty limitation of CREATE SEQUENCE and ALTER SEQUENCE, I would not have mentioned my INCR function. That limitation is this: the START WITH value must be a numeric literal. I tried to retrieve the largest sequence number from FILEA into a variable and set the sequence to the next integer value, but no soap. However, I was able to make it work with INCR. Here’s a dynamic compound statement that worked properly.

begin

   declare  NextSeq   integer;

   values (select max(field1) from filea) into NextSeq;

   insert into filea
      select incr(NextSeq), datab
        from fileb
       order by datab;
end

I’m very grateful for the functionality that IBM packs into the world’s best relational database management system. I’m also grateful that I can write my own code when necessary. We have the best of both worlds.

RELATED STORIES

Numbering Rows in an SQL Result Set

CREATE SEQUENCE