Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    5

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

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    5
    its me only.i have postaed it on the forum

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    INSERT INTO target_table (seq_no, fsa)
    SELECT seq_tar.nextval, fsa
    FROM source_table;

  5. #5
    Join Date
    Nov 2007
    Posts
    5

    insert into table with value from other table

    hi,the code has been further modified as
    test@ISNS>create table source (fsa varchar2(10),sal number);
    Table created.
    test@ISNS>create table target(seq_no number,fsa varchar2(10),rec_ct number);
    Table created.
    test@ISNS>insert into target(seq_no,fsa,rec_ct)
    2 select SEQ_TAR.nextval, fsa, sum(sal)
    3 from source group by fsa;

    select SEQ_TAR.nextval, fsa, sum(sal)
    *
    ERROR at line 2:
    ORA-02287: sequence number not allowed here
    Kindly help me.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    INSERT INTO target_table (seq_no, fsa, rec_ct)
    SELECT seq_tar.nextval, t.fsa, t.sal
    FROM (SELECT fsa, sum(sal) as sal
              FROM source_table
              GROUP BY fas);

  7. #7
    Join Date
    Nov 2007
    Posts
    5

    insert into table with value from other table

    Thanks .It worked now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •