How to get the Values of recently inserted columns

问题:

I am not able to use the recently inserted Quantity value of #ACCT table in the Select statement to be used like

ser.ServiceRate * Quantity  

Every time I get the ERROR

Cannot insert the value NULL into column 'Amount'

I am just a beginner so any pointers to solve this would help.

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

回答1:

You can use a CTE in order to capture the NEWID() value created for every record of table dbo.[Service]. Then use this CTE to do the 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