如何从临时表中获取最近插入的列的值？

admin

如何从临时表中获取最近插入的列的值？

sql

我无法在Select语句中使用表的最近插入的Quantity值#ACCT来像

ser.ServiceRate * Quantity

每次我得到错误

无法将值NULL插入“金额”列中

我只是一个初学者，所以解决这个问题的任何指示都将有所帮助。

DECLARE @MinReservationId INT = (SELECT MIN(f.ReservationId) FROM dbo.Reservation AS f)
DECLARE @MaxReservationId INT = (SELECT MAX(f.ReservationId) FROM dbo.Reservation AS f)
DECLARE @QuantityNew INT

WHILE @MinReservationId <= @MaxReservationId
BEGIN
    CREATE TABLE #Acct
    (
        ServiceId INT,
        Quantity INT --> I WANT THIS VALUE TO BE USED BELOW
    )

    INSERT INTO dbo.[Transaction]
(
    ReservationId,
    ServiceId,
    Rate,
    Quantity,
    Amount      
)   
OUTPUT inserted.ServiceId,Inserted.Quantity
INTO #Acct
(
    ServiceId,
    Quantity
)
SELECT 
    @MinReservationId,
    ser.ServiceId,
    ser.ServiceRate,
    ABS(CHECKSUM(NEWID())%3) + 1,
    ser.ServiceRate * (SELECT acc.Quantity from #Acct as acc) -> QUANTITY from #ACCT should be used here 
FROM dbo.[Service] AS ser

SELECT @MinReservationId=@MinReservationId+1
Drop table #Acct
END

阅读 169

2021-07-01

共1个答案

admin

您可以使用aCTE来捕获NEWID()为表的每个记录创建的值dbo.[Service]。然后使用它CTE来执行以下操作INSERT：

;WITH ToInsert AS 
(
   SELECT ServiceId ,
          ServiceRate,
          ABS(CHECKSUM(NEWID())%3) + 1 AS Quantity 
   FROM dbo.[Service] 
)
INSERT INTO dbo.[Transaction]
(
    ReservationId,
    ServiceId,
    Rate,
    Quantity,
    Amount      
)   
SELECT @MinReservationId,
       ServiceId,
       ServiceRate,
       Quantity,
       ServiceRate *  Quantity
FROM ToInsert

2021-07-01