How to get IDENTITY value of inserted rows in SQL Server

In my previous write-up about SCOPE_IDENTITY() troubles I spoke of there being a better method of getting the ID of a newly inserted row in SQL Server which does not depending on SCOPE_IDENTITY(), @@IDENTITY, or PDO::lastInsertId. That method is to use the OUTPUT clause as part of your INSERT statement and then running your query using either PDO::query or PDO::prepare/PDOStatement::execute.

Using an OUTPUT clause has a number of advantages over the other more commonly talked about options. Namely, there's no way to get an incorrect value from it which is a big deal. Secondly it works with multi-row inserts and can return each row's ID rather than just a single ID. Thirdly, you can return other data besides just the ID which can make post-insert processing easier.

So how does this output clause work you might ask? Let's look at a few different examples to give you an idea of how to use it. In each of the examples below, assume that $db is an instance of PDO connected to SQL Server. Here's the schema I'll be using for each of the examples.

CREATE TABLE sensor (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , name VARCHAR(20)
);
INSERT INTO sensor (name) VALUES ('Processor'),('Chipset'),('Graphics'),('Hard drive')

CREATE TABLE system (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , name VARCHAR(20) NOT NULL
    , added DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE system_readings (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , system_id INT NOT NULL
    , sensor_id INT NOT NULL
    , reading DECIMAL(5,2)
);

The typical case

In the typical case, you're only INSERTing a single row and only need to know that rows ID value after the insert. This is super simple:

$sql = "INSERT INTO system (name) OUTPUT INSERTED.id VALUES (?)";
$stmt = $db->prepare($sql);
$stmt->execute(['Hammond-PC']);

$id = $stmt->fetchColumn(0);

echo "{$name} has been successfully added as ID#{$id}\n";

See? Super simple. Within the OUTPUT clause exists a virtual table called INSERTED which refers to the newly inserted rows. This virtual table contains all the same columns as the real table, so you can choose to output any column within the table. For example, if we wanted to get the date the system was added and echo that as well, we could also select that column as part of the OUTPUT clause.

$name = 'My PC';

$sql = "INSERT INTO system (name) OUTPUT INSERTED.id, INSERTED.added VALUES (?)";
$stmt = $db->prepare($sql);
$stmt->execute([$name]);

$result = $stmt->fetch();

echo "{$name} has been successfully added as ID#{$result['id']} on {$result['added']}\n";

One could even use INSERTED.* to select everything from the newly inserted row if desired, however it is not recommended. Always select only what you need and do so by name so your code is easy to understand.

All at once

The next level beyond the typical case involves inserting multiple rows at once. This could be done either with a bunch of VALUES sets or by using something like INSERT INTO ... SELECT. Either way, the OUTPUT clause works the same way and will just give you multiple rows in the result set. Loop through the results just like you would the results of a typical SELECT query.

$allTheNames = ['O\'Neil-PC', 'Jackson-PC', 'Carter-PC', 'Teal\'c-PC'];
$placeholders = implode(',', array_fill(0, count($allTheNames), '(?)'));

$sql = "INSERT INTO system (name) OUTPUT INSERTED.id, INSERTED.name, INSERTED.added VALUES {$placeholders}";
$stmt = $db->prepare($sql);
$stmt->execute($allTheNames);

foreach ($stmt as $row){
    echo "{$row['name']} has been successfully added as ID#{$row['id']} on {$row['added']}\n";
}

The advanced stuff.

SQL Server also has the MERGE statement which is useful if you have data which you need to either INSERT if it's missing, or UPDATE if it already exists. Let's use our sensor_readings table as an example.

In our sensor_readings table we record values for each sensor. If no reading has been done before we need to INSERT the row, but if a reading already exists we want to UPDATE it to the latest value.

$sql = "INSERT INTO system (name) OUTPUT INSERTED.id VALUES ('Quinn-PC')";
$quinnId = $db->query($sql)->fetchColumn(0);

$readings = [['Processor', 26], ['Chipset', 48], ['Graphics', 47], ['Hard drive', 41]];

$placeholders = implode(',', array_fill(0, count($readings), '(?,?,?)'));
$params = array_reduce($readings, function($carry, $v) use ($quinnId){
    $carry[] = $v[0];
    $carry[] = $v[1];
    $carry[] = $quinnId;
    return $carry;
}, []);


$sql = "
;MERGE INTO system_readings
USING (
    SELECT sensor.id as sensor_id, system_id, reading
    FROM (VALUES {$placeholders}) data (name, reading, system_id)
    INNER JOIN sensor ON sensor.name = data.name
) src
ON src.sensor_id=system_readings.sensor_id AND system_readings.system_id=src.system_id
WHEN MATCHED THEN UPDATE
SET
    reading = src.reading
WHEN NOT MATCHED THEN INSERT (
    sensor_id
    , system_id
    , reading
) VALUES (
    src.sensor_id
    , src.system_id
    , src.reading
)
OUTPUT INSERTED.id as id
;";

$stmt = $db->prepare($sql);
$stmt->execute($params);
foreach ($stmt as $result){
    echo "Reading added as ID#{$result['id']}\n";
}