在transaction SQL表中插入未计算的Id

Question

我有一个存储过程，在这里我从C#中的API接收JSON形式的数据。我将数据插入两个表中，如下所示：

INSERT INTO dbo.ServiceRequestHeader(SubscriptionId, CustomerAccountId, ModifiedBy)
OUTPUT Inserted.ServiceRequestHeaderId INTO @TempT
SELECT
SubscriptionId,
CustomerAccountId,
ModifiedBy
FROM
OpenJson(@JsonServiceRequest)
WITH 
(SubscriptionId TinyInt,
CustomerAccountId Int)
SELECT @TempId = Id FROM @TempT   
INSERT INTO dbo.ServiceRequest(ServiceRequestId, ServiceRequestHeaderId, SubscriptionId)
SELECT
@TempId, -- <= Here I need to modify the serviceRequestHeaderId
@TempId,
SubscriptionId
FROM
OpenJson(@JsonServiceRequest, '$.ServiceRequest')
WITH (SubscriptionId TinyInt,
...)

问题是serviceRequestId不是一个计算字段，它是一个依赖于ServiceRequestHeaderId的特殊情况。

示例：

• 如果ServiceRequestHeaderId = 1000，则ServiceRequestId将是1000 001、1000 002…N

这是我无法找到的方法

Answer 1

您可以生成如下所示的servicerequestid。我正在使用带有000的FORMAT函数来填充0到3位数字。如果需要四位数字，请使用0000。

SELECT @TempId = Id FROM @TempT   
INSERT INTO dbo.ServiceRequest(ServiceRequestId, ServiceRequestHeaderId, SubscriptionId)
SELECT
CONCAT(@TempId,FORMAT(ROW_NUMBER() OVER(ORDER BY (SELECT null)),'000')) AS ServiceRequestId, -- <= Here I need to modify the serviceRequestHeaderId
@TempId,
SubscriptionId
FROM
OpenJson(@JsonServiceRequest, '$.ServiceRequest')
WITH (SubscriptionId TinyInt,
...)

你会得到下面这样的东西：

+------------------+
| ServiceRequestId |
+------------------+
|          1000001 |
|          1000002 |
|          1000003 |
+------------------+

Answer 2

使用CTE计算每个请求的行号，然后从中构建id，例如

with MyCTE as (
select
SubscriptionId
-- Order by whatever makes business sense to you
, row_number() over (order by SubscriptionId) rn
from openjson(@JsonServiceRequest, '$.ServiceRequest')
with (
SubscriptionId tinyint,
...
)
)
insert into dbo.ServiceRequest (ServiceRequestId, ServiceRequestHeaderId, SubscriptionId)
-- Put whatever logic you like here to calculate a row number based id
select convert(varchar(4),@TempId) + ' ' + case when rn >= 100 then convert(varchar(3),rn) when rn > 10 then '0' + convert(varchar(2),rn) else '00' + convert(varchar(1),rn) end
, @TempId, SubscriptionId
from MyCTE;