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