Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Lahore, Pakistan
    Posts
    5

    Question Unanswered: insert and select in one statement

    using MS Sql server and VB i can execute two queries Insert and select in one statement e.g. (insert into (....) values (...) Select @@Identity).
    how can i do the same thing using Oracle and VB. ???
    It gives error. here's what i want to do.
    (insert into table1 (...) values (...) Select table1_sequence.currval from dual )

    Khurram

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    insert into table_a (column list...) select column list ... from table_b

    Alan

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you have to use "VALUES" and "SELECT" in the same statement....


    insert into table1 (col1,col2,...) values (val1,(SELECT table1_sequence.nextval from dual),val3...);

    HTH
    Gregg

  4. #4
    Join Date
    Nov 2003
    Location
    Lahore, Pakistan
    Posts
    5
    what i want is insert a record and get the generated value in one statement. ADO supports compound statements.
    when i write two sql commands in one string oracle gives error.. it works with sql server .

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by gbrabham
    If you have to use "VALUES" and "SELECT" in the same statement....


    insert into table1 (col1,col2,...) values (val1,(SELECT table1_sequence.nextval from dual),val3...);

    HTH
    Gregg
    SQL> create table t (col1 int, col2 int);

    Table created.

    SQL> create sequence seq_t start with 1;

    Sequence created.

    SQL> insert into t (col1, col2) values (10,(select seq_t.nextval from dual));
    *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here

    Is this the error you are talking about ?

  6. #6
    Join Date
    Nov 2003
    Location
    Lahore, Pakistan
    Posts
    5
    i have a trigger that inerts a value in column. i have to get the inserted value.
    and i want to do it in one statement (compound statement). i am working in VB and using OLEDB.
    insert into t1 () values() Select t1_seq.currval from dual

    ADO can handle multiple recordsets but oracle give error when i execute two queries in one statement. if i put ' ; ' between queries it gives syntex error;

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This is the syntax, but I don't know how it woks with VB:

    insert into t1 (...) values (...) returning <columname> into <variable>;

Posting Permissions

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