This is my first time using T-SQL
I am currently trying to get the last inserted ID from an INSERT statement in T-SQL using this query:
INSERT INTO TICKET( STATE, RECORD_DATE, ID_USER, TICKET_TYPE, TICKET_COM) OUTPUT INSERTED.ID_TICKET AS lastId VALUES ( 1, CONVERT(datetime, '24/02/2022 09:25:53'), 100, 1, 'It does not work')
As you’ve guessed, ID_TICKET is the identity, with auto_increment. When I run this through MS SQL server management studio, I get the intended result : one row, with a unique lastId column containing the value of the last inserted id.
However on PHP, when I am running this using the query()
method from PEAR database, I am running into some issue.
From reading the documentation, DB->query()
will only return a resource in case of a SELECT query, while an INSERT query will just return a DB_OK type of answer: which is exactly what I am getting in PHP.
Hence my question : how can I retrieve the OUTPUT from an INSERT statement with PEAR DB?
I’d like to continue using PEAR, as I am adding functionality to an existing intranet heavily relying on it. It is hosted on IIS 7 with SQL Server 9.0 using PHP 5.2.9 and PEAR DB 1.1.2.2.
Advertisement
Answer
Finally, I just did a simple INSERT by removing the OUTPUT INSERTED.ID_TICKET AS lastId
line.
I then did a second query() call immediately after : SELECT @@IDENTITY as lastId
and it now gives the desired results.
Pretty much what Álvaro González suggested.
Now, following Dan Guzman sugestion and adding SET NOCOUNT ON;
at the beggining of my original INSERT
with the OUTPUT
clause indeed solved the problem !
I now get a result set with a unique row and column lastId as desired.
My hat off to both of you.