How to insert columns with adding sequence column?

Question

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?

Gordon Linoff · Accepted Answer · 2016-07-02 01:56:08Z

3

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 . . .;

answered Jul 2, 2016 at 1:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peterpepo · Accepted Answer · 2016-07-02 11:07:53Z

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)

wolfrevokcats · Accepted Answer · 2016-07-02 06:52:02Z

2

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.

edited Jul 2, 2016 at 6:52

answered Jul 2, 2016 at 6:42

wolfrevokcats

2,1101 gold badge14 silver badges13 bronze badges

Comments

user5683823 · Accepted Answer · 2016-07-02 02:09:33Z

1

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;

answered Jul 2, 2016 at 2:09

user5683823

3 Comments

amyassin Over a year ago

No, I already has the table but those are new rows I wanna insert. I am retrieving from couple other tables and want to add a sequence to.

user5683823 Over a year ago

... but then you need to make sure the new numbers don't clash with already existing numbers in the sequence?

amyassin Over a year ago

You are right. Luckily in this particular scenario col1 is a new unique value just for this particular insert, so other sequences won't clash for they have a different col1.

Collectives™ on Stack Overflow

How to insert columns with adding sequence column?

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related