Oracle inserting with different position of attributes-CodePudding

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!!

CodePudding user response：

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>