Search code examples
sqlsql-server

How to Insert Select from last table auto increment Id


I have two tables as shown here:

enter image description here

I need to insert some data by a stored procedure as below code:

ALTER PROCEDURE [dbo].[DeviceInvoiceInsert]
    @dt AS DeviceInvoiceArray READONLY
AS
    DECLARE @customerDeviceId BIGINT
    DECLARE @customerId BIGINT
    DECLARE @filterChangeDate DATE
BEGIN
    SET @customerId = (SELECT TOP 1 CustomerId FROM @dt 
                       WHERE CustomerId IS NOT NULL)
    SET @filterChangeDate = (SELECT TOP 1 filterChangeDate FROM @dt)

    INSERT INTO CustomerDevice (customerId, deviceId, deviceBuyDate, devicePrice)
        SELECT customerId, deviceId, deviceBuyDate, devicePrice 
        FROM @dt 
        WHERE CustomerId IS NOT NULL

    SET @customerDeviceId = SCOPE_IDENTITY()

    INSERT INTO FilterChange (customerId, filterId, customerDeviceId, filterChangeDate)
        SELECT @customerId, dt.filterId, @customerDeviceId, @filterChangeDate 
        FROM @dt AS dt
END

The problem is that when the procedure wants to insert data into the FilterChange table, the @customerDeviceId always has the last IDENTITY Id.

How can I figure out this problem?

Update Thanks for @T N answer but his solution is just to insert one filter per device, so in my case, there can be many filters per device


Solution

  • Thanks to @TN, his solution is just to insert one filter per device, so in my case, there can be many filters per device, So I just manipulate the last part to solve the problem. Also the corrected @dt value is like this:

    enter image description here

    As you can see, there are many filters per device, and All customers are the same because per invoice belongs to one customer so I had to mark the rest repetitive devices with null to group filters per device.

    Here is the corrected code, Thanks by @tn:

        -- Example showing MERGE (instead of INSERT) to capture a combination of
    -- source and inserted data in an OUTPUT clause.
    
    CREATE TABLE CustomerDevice (
        customerDeviceId INT IDENTITY(1,1),
        customerId INT,
        deviceId INT,
        deviceBuyDate DATE,
        devicePrice NUMERIC(19,4)
    )
    CREATE TABLE FilterChange (
        customerId INT,
        filterId INT,
        customerDeviceId INT, 
        filterChangeDate DATE
    )
    
    DECLARE @dt TABLE (
        customerId INT,
        deviceId INT,
        deviceBuyDate DATE,
        devicePrice NUMERIC(19,4),
        filterId INT,
        filterChangeDate DATE
    )
    INSERT @dt
    VALUES
        (3, 1, '2023-01-01', 111.11, 1, '2023-02-01'),
        (NULL, 1, '2023-01-02', 222.22, 2, '2023-02-02'),
        (NULL, 1, '2023-01-03', 333.33, 3, '2023-02-03'),
        (NULL, 1, '2023-01-03', 333.33, 4, '2023-02-03'),
        (3, 2, '2023-01-04', 333.33, 1, '2023-02-04'),
        (NULL, 2, '2023-01-04', 333.33, 2, '2023-02-04'),
        (NULL, 2, '2023-01-04', 333.33, 3, '2023-02-04'),
        (NULL, 2, '2023-01-04', 333.33, 4, '2023-02-04')
    
    -- Procedure body
    
        DECLARE @customerId BIGINT
    
        SET @customerId = (SELECT TOP 1 CustomerId FROM @dt WHERE CustomerId IS NOT NULL)
      
       DECLARE @FilterChangeData TABLE (
            customerId INT,
            deviceId INT,
            filterId INT,
            customerDeviceId INT,
            filterChangeDate DATETIME
        )
    
        MERGE CustomerDevice AS TGT
        USING (SELECT * FROM @dt WHERE CustomerId IS NOT NULL) AS SRC
            ON 1 = 0 -- Never match
        WHEN NOT MATCHED THEN
            INSERT (customerId, deviceId, deviceBuyDate, devicePrice)
            VALUES (SRC.customerId, SRC.deviceId, SRC.deviceBuyDate, SRC.devicePrice) 
            OUTPUT SRC.customerId,SRC.deviceId, SRC.filterId, INSERTED.customerDeviceId, SRC.filterChangeDate
                INTO @FilterChangeData;
    
       INSERT INTO FilterChange (customerId, filterId, customerDeviceId, filterChangeDate)
            SELECT @customerId, dt.filterId, fcd.customerDeviceId, dt.filterChangeDate 
            FROM @dt AS dt INNER JOIN @FilterChangeData AS fcd
            ON fcd.deviceId = dt.deviceId
    
    
    -- End procedure body
          
    SELECT * FROM @dt
    SELECT * FROM CustomerDevice
    SELECT * FROM FilterChange
    

    Result show in, https://dbfiddle.uk/yf7z_wqr