need sql syntax

get last entered row number

[txbakers]

I know I've seen this, but can't find it in the search of the site.

I insert a row into one table, which has an auto_generated row number (I call it the rrn).

I need to get this number and insert a row into another table with that number as the key.

I thought I saw an SQL command which will let me do this in one step. Right now I insert the new line into table 1, then run a recordset to get the rrn of the new record, then insert into table 2.

Isn't there a more efficient way?

Thanks.

[moltar]

I am not familiar with MS, but think of last row + 1

[markus007]

select @@identity

[txbakers]

Thanks, but I'm using mySql not SqlServer. We don't have the @@identity command.

[WebJoe]

txbakers, I had the same problem in a vb projet i was working on. In my case, because it had to work on SQLServer, Oracle, Access and SQLBase, I did the following (pseudo-code):

Begin Trans
Insert Into TableA (Fld2, Fld3, Fld4) Values ('Val2', 'Val3', 'Val4')
Select rrn From TableA Where Fld2='Val2' and Fld3='Val3' and Fd4='Fld4')
Insert Into TableB (ForeignKey, Fld3, Fld4) Values (rrn, 'Val3', 'Val4')
Commit Trans