How to insert columns with adding sequence column?

2
votes

In an Oracle database (11gR2), I have a table my_table with columns (sequence, col1, col2, col3). I want to insert values into the table that are queried from other tables, i.e. insert into my_table select <query from other tables>. The problem is that the primary key is the four columns, hence I need to add a sequence starting from 0 up till the count of the rows to be inserted (order is not a problem).

I tried using a loop like this:

DECLARE
    j    NUMBER;
    r_count number;
BEGIN
    select count(1) into r_count from <my query to be inserted>;
    FOR j IN 0 .. r_count
    LOOP
        INSERT INTO   my_table
              select <my query, incorporating r_count as sequence column> ;
    END LOOP;
END;

But it didn't work, actually looped r_count times trying to insert the entire rows every time, as logically it shall do. How can I achieve the expected goal and insert rows with adding a sequence column?

4
sqloracleplsqloracle11g

4 Answers

2
votes

Let's create table with sample data (to simulate your source of data)

-- This is your source query table (can be anything)
CREATE TABLE source_table
  (
    source_a VARCHAR(255),
    source_b VARCHAR(255),
    source_c VARCHAR(255)
  );

insert into source_table (source_a, source_b, source_c) values ('A', 'B', 'C');
insert into source_table (source_a, source_b, source_c) values ('D', 'E', 'F');
insert into source_table (source_a, source_b, source_c) values ('G', 'H', 'I');

Then create target table, with id and 3 data columns.

-- This is your target_table
CREATE TABLE target_table
  (
    id       NUMBER(9,0),
    target_a VARCHAR2(255),
    target_b VARCHAR2(255),
    target_c VARCHAR2(255)
  );

-- This is sequence used to ensure unique number in 1st column
CREATE sequence target_table_id_seq start with 0 minvalue 0 increment BY 1;

Finally, perform insert, loading id from sequence, rest of the data from source table.

INSERT INTO target_table
SELECT target_table_id_seq.nextval,
  source_a,
  source_b,
  source_c
FROM source_table;

Results might look like

1   A   B   C
2   D   E   F
3   G   H   I

If you added some values later, they will continue with numbering 4,5,6 etc.. Or do you want to get order only inside the group ? Thus if you added 2 more rows JKL and MNO, target table would look like this

1   A   B   C
2   D   E   F
3   G   H   I
1   J   K   L
2   M   N   O

For that you need different solution (don't even need sequencer)

SELECT
  RANK() OVER (ORDER BY source_a, source_b, source_c),
  source_a,
  source_b,
  source_c
FROM source_table;

Technically you could use ROWNUM directly, BUT I opt for RANK() OVER analytical function due to consistent result. Please note, that this will breach your complex primary key if you try to insert the same rows twice (My first solution doesn't)

3
votes

Don't do this in a loop. Just use row_number():

INSERT INTO my_table(seq, . . .)
     select row_number() over (order by NULL) - 1, . . .
     from . . .;
2
votes

Clearly, you should use Oracle sequence.
First, create a sequence: 

create sequence seq_my_table start with 0 minvalue 0 increment by 1;

Then use it:

INSERT INTO   my_table (sequence, ...) 
select seq_my_table.nextval, <the rest of my query>;

Sequence numbers will be inserted in succession.

1
votes

So, you already have the table, it has the required number of rows, and now you want to add numbers from 0 to total number of rows minus one in the column named sequence? (perhaps not "sequence" but something less likely to clash with Oracle reserved words?)

Then this should work:

update my_table set seq = rownum - 1;