Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Question Unanswered: SQL to Select Inserted Records

    I'm wondering if there's a way to return records created by an Insert statement. The insert statement itself is easy, but I then need to select the records I just created, specifically the field with a system generated ID. Since I don't know that ID before the records are inserted (obviously) I can't think of any easy way to do this. Any suggestions?

  2. #2
    Join Date
    Apr 2003
    Location
    Columbia, SC
    Posts
    7

    Re: SQL to Select Inserted Records

    Originally posted by afterglow
    I'm wondering if there's a way to return records created by an Insert statement. The insert statement itself is easy, but I then need to select the records I just created, specifically the field with a system generated ID. Since I don't know that ID before the records are inserted (obviously) I can't think of any easy way to do this. Any suggestions?
    If you could add another field, say insert date, then you would be able to pull all files with a date greater than...
    or a field could be added that would be an insert flag. If the flag is set to true, then pull. Once pulled set flag to false...
    I hope this is of some help.

  3. #3
    Join Date
    Nov 2002
    Posts
    13

    Re: SQL to Select Inserted Records

    Originally posted by mhozey
    If you could add another field, say insert date, then you would be able to pull all files with a date greater than...
    or a field could be added that would be an insert flag. If the flag is set to true, then pull. Once pulled set flag to false...
    I hope this is of some help.
    I was thinking about doing it that way, but I was hoping there was some nifty SQL trick to get around adding a field just for that and doing an extra update. Thanks for the reply!

  4. #4
    Join Date
    Sep 2003
    Location
    Wisconsin, USA
    Posts
    34

    ID me

    I don't know much other than postgres, but if the ID you want is from a serial field (serial is like an auto-number, or auto-increment in other DB's), you can find out the ID's BEFORE you insert the data, and then insert the data with the IDs handed to you.

    Basically, postgres stores it's sequences for the serial fields in a seperate entity. Thus if you have a table MyFriends with data:
    Id | name
    ------------
    1 joe
    2 bob
    3 sue

    You can SELECT nextval('myfriends_id_seq'); and it will return 4. Now, the next person who does an insert (normal insert, with nextval, or otherwise) will get 5.... so 4 is yours to use! Then instead of just INSERT INTO MyFriends(name) VALUES ('frank'); you would use the ID handed to you ... INSERT INTO MyFriends(ID, name) VALUES (4, 'frank');

    You can grab a swath of id's this way before-hand, if you have multiple lines to edit.


    I don't know if any of this applies to anything but postgres, but maybe it's food for thought (Maybe you can find a way to do something like this in your DB.)

    Otherwise, yeah, you'll have to resort to something a little less elegant like a timestamp or magic-number field.

  5. #5
    Join Date
    Oct 2003
    Location
    Belgium
    Posts
    2

    Re: SQL to Select Inserted Records

    Originally posted by afterglow
    I'm wondering if there's a way to return records created by an Insert statement. The insert statement itself is easy, but I then need to select the records I just created, specifically the field with a system generated ID. Since I don't know that ID before the records are inserted (obviously) I can't think of any easy way to do this. Any suggestions?
    After the insert-statement:

    SELECT MAX(id) FROM tbl
    - id : is the fieldname of the ai-field
    - tbl : your table-name : )

    response.write RS(0)
    - To display/use latest added record.
    - Keep in mind: the id-field has to be unique! The 'MAX' retrieves the highest number from the table.
    Last edited by achje; 10-02-03 at 04:24.

  6. #6
    Join Date
    May 2003
    Posts
    45

    Re: SQL to Select Inserted Records

    Well There is way for this using rowid(is a unique number for each row inserted)

    Before insert you need perform this select

    select max(rowid) from table

    this will return something like AAADFLAABAAAi1YAAF

    so after you insert the record you can perforn this select

    SELECT * FROM table WHERE
    ROWID>'AAADFLAABAAAi1YAAF';

    cheeers

  7. #7
    Join Date
    Nov 2002
    Posts
    13

    Re: SQL to Select Inserted Records

    Originally posted by itsmanyam
    Well There is way for this using rowid(is a unique number for each row inserted)

    Before insert you need perform this select

    select max(rowid) from table

    this will return something like AAADFLAABAAAi1YAAF

    so after you insert the record you can perforn this select

    SELECT * FROM table WHERE
    ROWID>'AAADFLAABAAAi1YAAF';

    cheeers
    I'll have to see if Sybase stores the IDs in another table a la postgres, it seems possible. Otherwise I'll do the max of that ID field, which i like a lot better than adding a field to track new records just because my app will be doing a lot of these transactions one after another, so the less updates to the DB I can do, the better. Thanks to everyone who gave input!

Posting Permissions

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