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.

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!

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.

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.

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!