SQL Server中OPENJSON插入时添加默认值及使用参数的方法

问题背景 #

我在创建存储过程，要把JSON数据插入到表中，这个表包含uniqueidentifier类型的ID字段和datetime类型的CreatedAt字段，遇到了以下问题：

问题1：省略必填字段报错 #

如果执行脚本时省略ID或CreatedAt，会触发错误：Column name or number of supplied values does not match table definition，示例脚本如下：

INSERT INTO [RequestsDetails]
SELECT *
  FROM OPENJSON(@json)
  WITH (
    RequestId        uniqueidentifier '$.RequestId',       
    QuoteId          uniqueidentifier '$.QuoteId',         
    SiteId           int              '$.SiteId',          
    SumBuilding      money            '$.SumBuilding'
  )

问题2：OPENJSON的WITH子句直接用函数报错 #

尝试在WITH子句里给ID赋值NEWID()、给CreatedAt赋值GETDATE()时，会报错：Incorrect syntax near NEWID，示例脚本如下：

INSERT INTO [RequestsDetails]
SELECT *
  FROM OPENJSON(@json)
  WITH (
    Id               uniqueidentifier NEWID(),
    RequestId        uniqueidentifier '$.RequestId',       
    QuoteId          uniqueidentifier '$.QuoteId',         
    SiteId           int              '$.SiteId',          
    SumBuilding      money            '$.SumBuilding',
    CreatedAt        datetime         GETDATE()
  )

更新问题：用存储过程参数@RequestId插入 #

存储过程里有参数@RequestId，想把它用到INSERT操作里，尝试的脚本如下：

@RequestId uniqueidentifier

INSERT INTO [Sace].[RequestsDetails] (
    RequestId        
    -- 省略其他字段  
)
SELECT *
  FROM OPENJSON(@json)
  WITH (
    RequestId        uniqueidentifier @RequestId
    -- 省略其他字段
  )

解决方案 #

1. 添加JSON中不存在的ID和CreatedAt字段 #

OPENJSON的WITH子句不支持直接写函数生成值，正确的做法是在SELECT列表里显式生成这些值，同时明确指定插入的列名（别用SELECT *，避免列数不匹配）：

INSERT INTO [RequestsDetails] (Id, RequestId, QuoteId, SiteId, SumBuilding, CreatedAt)
SELECT 
    NEWID() AS Id,
    RequestId,
    QuoteId,
    SiteId,
    SumBuilding,
    GETDATE() AS CreatedAt
FROM OPENJSON(@json)
WITH (
    RequestId        uniqueidentifier '$.RequestId',       
    QuoteId          uniqueidentifier '$.QuoteId',         
    SiteId           int              '$.SiteId',          
    SumBuilding      money            '$.SumBuilding'
)

这么做的优势：

• 明确指定插入列，就算后续表结构变更，也不容易出错
• 符合SQL语法规范，在数据查询阶段生成所需的默认值

2. 使用存储过程参数@RequestId #

同样要在SELECT列表里引用参数，不能直接在WITH子句里赋值：

CREATE PROCEDURE InsertRequestsDetails
    @RequestId uniqueidentifier,
    @json NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [Sace].[RequestsDetails] (RequestId, QuoteId, SiteId, SumBuilding, Id, CreatedAt)
    SELECT 
        @RequestId AS RequestId, -- 直接用参数值
        QuoteId,
        SiteId,
        SumBuilding,
        NEWID() AS Id,
        GETDATE() AS CreatedAt
    FROM OPENJSON(@json)
    WITH (
        QuoteId          uniqueidentifier '$.QuoteId',         
        SiteId           int              '$.SiteId',          
        SumBuilding      money            '$.SumBuilding'
    )
END

如果JSON里本来就没有RequestId字段，WITH子句里就不用定义它；如果JSON里有这个字段但你想覆盖它，直接在SELECT里用参数值替换就行。

内容的提问来源于stack exchange，提问作者Enrico