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
[code=oracle]

insert into
target(seq_no,f sa,rec_ct)
select
(SEQ_TAR.nextva l,
fsa,
sum(sal)
from source
group by fsa
)
[/code]


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,f sa,rec_ct)
select
(SEQ_TAR.nextva l,
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.