Insert into a table with sequence and a value from other table

[hgriva]

Hi,

I have a table TARGET whose structure is

seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)

Table SOURCE structure is
-------------------------
fsa varchar2(10)

my requirement is
i have to insert into the TARGET table the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.

[Dave44]

Hi,

I have a table TARGET whose structure is

seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)

Table SOURCE structure is
-------------------------
fsa varchar2(10)

my requirement is
i have to insert into the TARGET table the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.

i fear this may be a school assignment and so i dont want to do your work for you as you will not learn anything that way.

it sounds like you just want to do an insert into a table rows from another table + the sequence. you have written the code to get the sequence value so just put the 2 together. insert into ... select .... If there are restrictions to which rows you want to insert then you will need to add a where clause to the select.

[hgriva]

i fear this may be a school assignment and so i dont want to do your work for you as you will not learn anything that way.

it sounds like you just want to do an insert into a table rows from another table + the sequence. you have written the code to get the sequence value so just put the 2 together. insert into ... select .... If there are restrictions to which rows you want to insert then you will need to add a where clause to the select.

Sorry i missed out a major part
So i'm once again giving the scenario


I have a table TARGET whose structure is

seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)
rec_ct number

Table SOURCE structure is
-------------------------
fsa varchar2(10)
sal number

my requirement is
i have to insert into the TARGET table the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.

like

 
insert into
target(seq_no,fsa,rec_ct)
select
(SEQ_TAR.nextval,
 fsa,
 sum(sal)
 from source
 group by fsa
 )
 

this is giving me a error.
Kindly help.

[Dave44]

Sorry i missed out a major part
So i'm once again giving the scenario


I have a table TARGET whose structure is

seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)
rec_ct number

Table SOURCE structure is
-------------------------
fsa varchar2(10)
sal number

my requirement is
i have to insert into the TARGET table the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.

like
insert into
target(seq_no,fsa,rec_ct)
select
(SEQ_TAR.nextval,
fsa,
sum(sal)
from source
group by fsa
)

this is giving me a error.
Kindly help.

yes, makes sense, invalid syntax. The opening bracket for the second set of brackets should be in front of the select (actually the second set isnt needed).

Elapsed: 00:00:00.59
[159]SQL> create table t (col1  varchar2(100));
 
Table created.
 
[159]SQL> insert into t (col1) (select 1 from dual);
 
1 row created.
 
Elapsed: 00:00:00.18
[159]SQL> insert into t (col1) select 1 from dual;
 
1 row created.
 
 

further though, your select statement should be executable without the insert portion (its a good way to test that it returns what you want as well). In your case you cant group by one field and select 2.

Elapsed: 00:00:00.01
[159]SQL> drop table t;
 
Table dropped.
 
Elapsed: 00:00:01.01
[159]SQL> create table t (col1  number, col2 varchar2(30), col3 varchar2 (30));
 
Table created.
 
Elapsed: 00:00:00.01
[159]SQL> create sequence t_seq;
 
Sequence created.
 
Elapsed: 00:00:00.04
[159]SQL> insert into t select t_seq.nextval,dummy,dummy from dual;
 
1 row created.
 
 

without using some sort of grouping function like max() or min() or sum() on the other columns you end up with this error

[159]SQL> select col1,col2,col3 from t group by col2;
select col1,col2,col3 from t group by col2
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
 

based on your requirement i dont think you need to group by anything. just insert the sequence and fsa field into the target table.