Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: PL/SQL insert into two tables

    Hi folks,

    I have two tables I want to insert into - tables A and B.
    The primary key (requestid) on Table A is an identity column so I can't manually insert into it; and I want to insert the same value for requestid into Table B.
    So I was wondering if there was a way to get the inserted value for requestid from Table A so I could insert it into Table B?

    Thanks for any help,
    Breen.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You can do this a number of ways.

    The first uses the variable @@identity which is the value of the last returned identity column on your current connection.

    This would be an example:
    Code:
    BEGIN TRANSACTION
    INSERT INTO t1 VALUES( ... )
    INSERT INTO t2 VALUES( @@identity, ... )
    COMMIT TRANSACTION
    The other method could be an insert trigger on t1 which inserts inserted.yourIdentityColumn to t2.

    P.S. it's not PL/SQL but T-SQL.

    (edit: fixed missing 'end transaction')
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks Matt,

    Will try it tomorrow.

    Sorry - T-SQL, still thinking Oracle.

    Cheers,
    Breen.

Posting Permissions

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