How to get Insert id in MSSQL in PHP?

Question

What the question title says. With a query such as SELECT @@IDENTITY AS ins_id, do I need to supply the table name or any other info to specify which table/database i'm talking about?

Answer 1

@@IDENTITY returns the most recent identity generated in the current session. In most cases you'll probably want to use SCOPE_IDENTITY instead, which returns the most recent identity generated in the current scope.

For example, if you insert a row into table1, but that insert fires a trigger which inserts a row into table2, then @@IDENTITY will return the identity from table2 whereas SCOPE_IDENTITY will return the identity from table1.

INSERT INTO my_table (my_column) VALUES ('test')

-- return the identity of the row you just inserted into my_table
-- regardless of any other inserts made by triggers etc
SELECT SCOPE_IDENTITY() AS ins_id

Answer 2

No; it works much like SELECT LAST_INSERT_ID() in mysql, retrieving the last identity value inserted. You may want to take a look at this in-depth examination for more on what you might want to be concerned about with it.

Answer 3

Here is code snippet somewhat based on Joomla code. $dbh is database connection (result of mssql_connect()). Key name (ID) is updated if you pass $keyName argument.

This code is using MSSQL keyword "OUTPUT" to get ID (or any required value) of inserted value.

function mssql_insertObject($table, &$object, $keyName = NULL)
{
    global $dbh;

    if($keyName) {
        $fmtsql = 'INSERT INTO '. $table .' ( %s ) OUTPUT INSERTED.' . $keyName . ' VALUES ( %s ) ';
    }
    else {
        $fmtsql = 'INSERT INTO '. $table .' ( %s ) VALUES ( %s ) ';
    }

    $fields = array();

    foreach (get_object_vars( $object ) as $k => $v) {
        if (is_array($v) or is_object($v) or $v === NULL) {
            continue;
        }

        if ($k[0] == '_') { // internal field
            continue;
        }

        $fields[] = $k;
        $values[] = "'" . str_replace("'", "''", $v) . "'";
    }

    $sql = sprintf( $fmtsql, implode( ",", $fields ) ,  implode( ",", $values ) );

    $query = mssql_query($sql, $dbh);

    if($query === false) {
        return false;
    }

    if(is_resource($query))
    {
        if($keyName) {
            $id = mssql_result($query, 0, 0);

            if($id) {
                $object->$keyName = $id;
            }
        }

        mssql_free_result($query);
    }

    return true;
}