Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Unanswered: Last Inserted row

    I am trying to access the row which was just inserted into a table. Will the following statements be equivalent of each other?

    STATEMENT 1:
    SELECT JobNumber
    from tblCustServiceHistoryHdr
    where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/


    STATEMENT 2:
    SELECT JobNumber
    from inserted
    where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Would you like to know the last row inserted by your session, or by all sessions ?

    About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.

    Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.

  3. #3
    Join Date
    Nov 2003
    Posts
    31
    Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

    But for now I will only need to know the inserted row for my session.

  4. #4
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: Last Inserted row

    How about introducing a new column to your table which will hold the time stamp for new rows inserted?
    Shadow to Light

  5. #5
    Join Date
    Nov 2003
    Posts
    31
    I thought the DateModified column acted like a timestamp....u know everytime there r updates to that particular row the DateModified column is altered using getdate().

  6. #6
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Why not put an Identity Seed field on your table ?

    Then do the insert through a stored procedure and pass back the identity field value.

    By the way, @@IDENTITY returns the last generated value for the newly inserted row.

  7. #7
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    What do you want to know the inserted row for ?
    Might help us with a solution

  8. #8
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by gayamantra
    Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

    But for now I will only need to know the inserted row for my session.
    Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.

Posting Permissions

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