如何使用php、SQL SERVER 2005从INSERT语句返回插入的行?

What is the easiest / most efficient way to get the entire row inserted after an INSERT statement?

在插入语句之后插入整个行，最简单/最有效的方法是什么?

I am pretty sure I could do this as follows:

我很确定我可以这样做:

$aQuery = "INSERT into myTable (a, b, c) VALUES (1, 'Hello', 'Goodbye')";
//the IDENTITY coloumn in myTable is named id
$result = sqlsrv_query($myConn, $aQuery );
if ($result) {
    $res = sqlsrv_query('SELECT LAST_INSERT_ID()');
    $row = sqlsrv_fetch_array($res);
    $lastInsertId = $row[0];
    $subQuery = "SELECT * FROM myTable where id = {$lastInsertId}";
    $subResult = sqlsrv_query($myConn, $subQuery);
    if ($subResult) {
        while($subrow = sqlsrv_fetch_array($subResult)) {
            echo($subrow ['id'] . ', '.
                 $subrow ['a'] . ', '.
                 $subrow ['b']);  //etc...
        }
     }
}

However, I am concerned about the possibility of another insert occurring just before my SELECT LAST_INSERT_ID() and thus messing up my logic to boot. How can I be certain that the last inserted id is truly the INSERT I called previously, and not something happening somewhere else?

但是，我担心在我的SELECT LAST_INSERT_ID()之前可能会出现另一个插入，从而导致启动逻辑混乱。我怎么能确定最后插入的id是我之前调用的插入，而不是其他地方发生的事情呢?

Is there a more appropriate way of doing this, perhaps a complete SQL solution (such that the query returns the row automatically rather than using PHP)?

是否有一种更合适的方法来实现这一点，也许是一种完整的SQL解决方案(比如查询自动返回行，而不是使用PHP)?

UPDATE: myTable DOES have an explicitly defined (and auto-incremented) identity column, named id.

更新:myTable确实有一个显式定义(并自动递增)的标识列，名为id。

3 个解决方案

#1

This will work:

这将工作:

"INSERT into myTable (a, b, c) OUTPUT Inserted.a, Inserted.b, Inserted.c VALUES (1, 'Hello', 'Goodbye')

#2

In Sql Server, you would use select @lastID=SCOPE_IDENTITY()

在Sql Server中，您将使用select @lastID=SCOPE_IDENTITY()

And @LastID will have the last id inserted for the current scope; therefore, if there was another insertion in the middle, you would still get the correct record on your select.

@LastID将会为当前范围插入最后一个id;因此，如果中间有另一个插入，您仍然可以在select中获得正确的记录。

Never use @@Identity for this or you may end up in a situation like you described.

千万不要使用@ identity，否则您可能会遇到您所描述的情况。

#3

If you were to use identity field (which maybe you should) there is a command called SCOPE_IDENTIY() which info you can find here: http://msdn.microsoft.com/en-us/library/ms190315.aspx

如果您要使用identity字段(可能您应该使用)，有一个名为SCOPE_IDENTIY()的命令，您可以在这里找到:http://msdn.microsoft.com/en-us/library/ms190315.aspx。

Since you do not use it, you do not have to select latest data since you have it when you insert, so just use same data instead of selecting.

由于您不使用它，所以您不必选择最新的数据，因为您在插入时已经有了它，所以只需使用相同的数据，而不必选择。

#1