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);