Results 1 to 3 of 3

Thread: get ID?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    get ID?

    Hi.
    I have a relationship table, 3 tables.


    Threads -> Replies - both of these need Users table as well.

    now

    Since on each table, the ID is autogenerated.
    Since I will be writing a stored procedure to insert a new thread, the new thread table has:

    ThreadID
    DateOfEntry
    ThreadName
    UserID

    And the thread text, the initial entry of this thread made by the thread creator, will be inserted into the table "Replies", giving it the ThreadID and so on.

    now...

    how can I give "Replies" the ThreadID?

    I mean, I cannot just insert a new record in "Threads", then do a query on the last entry inserted and or also search it based on the date and time as that is not the best way of doing things.

    how can I get the ID of the record I am ABOUT to insert? is this possible?

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  2. #2
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: get ID?

    you can use @@IDENTITY - Returns the last-inserted identity value.

    ex:
    DECLARE @NewID int

    INSERT INTO tblThreads(DateOfEntry, ThreadName, UserID)
    VALUES(@Date, @ThreadName, @User)

    SET @NewID = @@IDENTITY

    --you can then use @NewID to be the threadID for the replies table..
    INSERT INTO tblReplies(ThreadID, ....)
    VALUES(@NewID, .....)
    Last edited by eimroda; Apr 18th, 2006 at 11:16 AM.
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: get ID?

    Thanks!

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

Posting Permissions

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



Click Here to Expand Forum to Full Width