Oracle inserting with different position of attributes

Question

Please let me know whether Oracle insert statement works if we place the attributes in a different sequence in terms of attribute positions. I am not able to test in dev environment so need expert opinion before I promote changes in PROD directly. Please help.

I am having the following tables:

tableA - col1, col2, col3, col4,col5

tableB - col1, col2, col4, col5

I need to pick the distinct values from tableB and insert into tableA by adding a sequence number to it.

Since distinct and sequence numbers don't work together in insert statements I am using an outer select statement.

Please let me know which of the following 2 options will work ??? If both don't work then please provide your suggestions as well.

option 1 - adding nextval in the outer select statement at the last and keeping col3 as the last position in insert

insert into tableA ( col1, col2, col4, col5, col3 ) select col1, col2, col4, col5, my_seq.nextval as col3 from ( select distinct col1, col2, col4, col5 from tableB );

option 2- adding nextval in the outer select statement in the same sequence and keeping col3 as also in the same position in insert

insert into tableA ( col1, col2, col3, col4, col5 ) select col1, col2, my_seq.nextval as col3, col4, col5 from ( select distinct col1, col2, col4, col5 from tableB );

thanking in advance!!

Answer 1

Both will work. It doesn't matter in which order you insert them, as long as you specify column names (in insert into) and match them in select that follows.

SQL> create sequence my_seq;

Sequence created.

SQL> create table tablea (deptno number, job varchar2(10), seq number);

Table created.

SQL> -- your first query
SQL> insert into tablea (deptno, job, seq)
  2    select deptno, job, my_seq.nextval
  3    from (select distinct deptno, job from emp);

9 rows created.

SQL> -- your second query
SQL> insert into tablea (seq, job, deptno)
  2    select my_seq.nextval, job, deptno
  3    from (select distinct deptno, job from emp);

9 rows created.

Result:

SQL> select * from tablea order by seq;

    DEPTNO JOB               SEQ
---------- ---------- ----------
        20 CLERK               1
        30 SALESMAN            2
        20 MANAGER             3
        30 CLERK               4
        10 PRESIDENT           5
        30 MANAGER             6
        10 CLERK               7
        10 MANAGER             8
        20 ANALYST             9
        20 CLERK              10
        30 SALESMAN           11
        20 MANAGER            12
        30 CLERK              13
        10 PRESIDENT          14
        30 MANAGER            15
        10 CLERK              16
        10 MANAGER            17
        20 ANALYST            18

18 rows selected.

SQL>

Why is it OK? Because there are 9 distinct combinations of [deptno, job] in Scott's EMP table.

SQL> select distinct deptno, job from emp;

    DEPTNO JOB
---------- ---------
        20 CLERK
        30 SALESMAN
        20 MANAGER
        30 CLERK
        10 PRESIDENT
        30 MANAGER
        10 CLERK
        10 MANAGER
        20 ANALYST

9 rows selected.

SQL>