Mysql_insert_id equivalent for SQL Server

[miraclemaker]

Does anyone know if there's an equivalent of Mysql_insert_id for SQL server?

 

[vbkris]

i dont think thats possible in SQL Server, i usually generate my own ids in SQLServer...

Known is handfull, Unknown is worldfull

 

[sleipnir214]

But then, you might ask this question on a SQL Server forum.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[miraclemaker]

I need to know how to do it through PHP so I can retrieve the last auto-incremented identity. Retrieving the last ID through Enterprise manager is not an option.

I've resorted to doing a query to find the row with the highest id that has the same values as the last row I inserted into the database.

 

[sleipnir214]

Getting the max of the id is not a good idea. All it takes is for another record to have been inserted by another instantiation of your script and your app barfs.


If the folk in the SQL Server forum give you an answer that involved a SQL query, you could use it in PHP couldn't you?

Ask in the SQL Server forum. Sure, they may tell you that it can't be done through PHP. But then again they may know something about SQL Server that we don't.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[miraclemaker]

You're correct, which is why I was hesitant to use this solution. It's not too bad though, as the other information that is added to the db includes the unique ID of the user, so fortunately the only way you could get the wrong id back would be if the same user was simultaniousely adding more rows, containing the same information.

 

[sleipnir214]

Why are you resisting asking the question of the very people in Tek-Tips most likely to be able to answer your original question?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[miraclemaker]

Well I've asked, but I think the answer I'm going to get is to use MAX()

 

[sleipnir214]

That may well be. However, they could tell you that:

SELECT @@identity

or something will return the autoincrement value deterministically.

But you won't know without asking.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ingresman]

Are you after the identity of the last insert you did in which case you can select the identity column with the key you inserted with e.g.

insert into fred(keyfield,val) value ('john',28);
select identitycol from fred where keyfield = 'john';

But if you want to get the current identity for the table independent of anything else you can issue a dbcc call of checkident(fred) and capture the output e.g

create table reg (id int);
insert into reg exec ('dbcc checkindent(fred,NORESEED) with no_infomsgs');
select id from reg;

Which according to books online for sqlserver2000 will tell you the current identity column value. If I were you Id check my syntax as I don't have SQLserver on my PC

I'd be interested yo know how you get on.

Regards

Kevin

 

[miraclemaker]

It was the first case scenario. I ended up doing something similar, but using MAX