How to 'SET IDENTITY_INSERT...' in Laravel.

發表於 | 分類於

通常在 SQL Server 如果要新增資料到含有 IDENTITY 欄位的 Table，且指定該欄位的值時，必需要先把 IDENTITY_INSERT 設成 OFF，才能正確的把資料 Insert 否則會出現錯誤訊息。

SQL statement 大概會長成下面這樣：

1
2
3
SET IDENTITY_INSERT TABLE_NAME ON;
INSERT INTO TABLE_NAME (id, name) VALUES (123, 'Austin Lin');
SET IDENTITY_INSERT TABLE_NAME OFF;

用 Laravel 會寫成下面這樣：

1
2
3
4
5
6
7
8
DB::statement('SET IDENTITY_INSERT users ON');

User::create([
    id => 123,
    name => 'Austin Lin',
]);

DB::statement('SET IDENTITY_INSERT users OFF');

然後你就會得到一個錯誤訊息 Cannot insert explicit value for identity column in table 'TABLE_NAME' when IDENTITY_INSERT is set to OFF.。下面提供兩個 Solution，不過Solution 1 雖然可以動，但這樣就無法使用 Model 來操作 Database 了，所以比較好的方式是 Solution 2

Solution 1 - 塞在同一個 statement 用 TRANSACTION 包起來

1
2
3
4
5
6
7
8
9
DB::statement("
    BEGIN TRANSACTION;

    SET IDENTITY_INSERT TABLE_NAME ON;
    INSERT INTO TABLE_NAME (id, name) VALUES (123, 'Austin Lin');
    SET IDENTITY_INSERT TABLE_NAME OFF;

    COMMIT;
");

Solution 2 - 把 PDO::SQLSRV_ATTR_DIRECT_QUERY 設成 true

1
2
3
4
5
6
7
8
9
10
11
12
$pdo = DB::connection()->getPdo();

$pdo->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY, true);
DB::statement('SET IDENTITY_INSERT users ON');

User::create([
    id => 123,
    name => 'Austin Lin',
]);

DB::statement('SET IDENTITY_INSERT users OFF');
$pdo->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY, false);