need quick help with sql

[TechBoyJK]

i have an insert statement that creates a new record. The primary key is an autoincrement, and I need to return that number during or right after the insert statement.

any advice?

 

[Gnurb]

there's a sql function for this: LAST_INSERT_ID()

if you're using php, you can use mysql_insert_id() in your php code

 

[ggavinmoss]

Originally posted by: Gnurb
there's a sql function for this: LAST_INSERT_ID()

if you're using php, you can use mysql_insert_id() in your php code

Won't that only work in MySQL?

-geoff

 

[TechBoyJK]

it needs to work with ms sql

 

[MichaelD]

Ah, that one is easy, dude!!

Text

Enjoy.

 

[OffTopic1]

I'm not sure if it work with MYSQL, but try the below:

SELECT MAX(ID) FROM tblName


Last insert id in autoincrement field from CodingForums.com

 

[Boogak]

Originally posted by: TechBoyJK
it needs to work with ms sql

SQL Server has a couple of different ways of grabbing last inserted IDs

SELECT @@IDENTITY;

SELECT SCOPE_IDENTITY();

There's a 3rd one that I don't remember off the top of my head, but I use SCOPE_IDENTITY() usually.

 

[TechBoyJK]

well I'm using coldfusion, so i wrapped two queries in a <cftransaction> tag.

I do the insert query, and within the same transaction tag, i run another query to find the max id of the table I just inserted to.

I'm still curious as to how to implement the select scope identity. do i use that during the same query that creates the record?

 

[Boogak]

Originally posted by: TechBoyJK
well I'm using coldfusion, so i wrapped two queries in a <cftransaction> tag.

I do the insert query, and within the same transaction tag, i run another query to find the max id of the table I just inserted to.

I'm still curious as to how to implement the select scope identity. do i use that during the same query that creates the record?

Yup, separate the Insert query and the Select query by a semicolon

INSERT INTO ...;
SELECT Scope_Identity();

It will return the Identity value back to your recordset after you fire off the query.

 

[PricklyPete]

Originally posted by: TechBoyJK
well I'm using coldfusion, so i wrapped two queries in a <cftransaction> tag.

I do the insert query, and within the same transaction tag, i run another query to find the max id of the table I just inserted to.

I'm still curious as to how to implement the select scope identity. do i use that during the same query that creates the record?

Stick the insert and retrieval of the value in a stored procedure and just run the stored procedure.