Database

create global temporary table MyTable (
  MyColumn INT,
  etc ...
  ) on commit preserve

TYPE primary_keys_tabtype IS TABLE OF NUMBER NOT NULL
      INDEX BY BINARY_INTEGER;
ClientIdList primary_keys_tabtype;

select A.*
from (
  select 'Some complex stuff'
  from SomeTable
) as A

blackjack2150 wrote:

I want to insert a record in a table and then get it's ID(which is an autonumber field) to use it as a reference in another table. What is the best approach?

blackjack2150 wrote:

It can be either Access, MySql or SQL Server. The only thing that changes to select the database engine is the connection string. Therefor I don't want to do any programming at the database side(like stored procedures).

blackjack2150 wrote:

The trivial solution is to do insert and after select the max value of the id, hoping that nothing was inserted in between the two operations.

SELECT Id, Column1, Column2, Column3, Etc
FROM MyTable
WHERE Column1 = @column1
AND Column2 = @Column2
AND Column3 = @Column3
ORDER BY Id DESC