How to Insert Select from last table auto increment Id-CodePudding

I have two tables as shown 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

CodePudding user response：

As mentioned above, using the OUTPUT clause is the best way to capture inserted IDENTITY or other implicitly assigned values. However, you also need to correlate this data with other values from your source table. As far as I know, this cannot be done using a regular INSERT statement, which only allows you to capture data from the target table via the INSERTED pseudo-table.

I am assuming that none of the explicitly inserted values in the first target table can be used to reliably uniquely identify the source record.

A workaround is to use the MERGE statement to perform the insert. The OUTPUT clause may then be used to capture a combination of source and inserted target data.

ALTER PROCEDURE [dbo].[DeviceInvoiceInsert]
    @dt AS DeviceInvoiceArray READONLY
AS
BEGIN
    -- Temp table to receive captured data from output clause
    DECLARE @FilterChangeData TABLE (
        customerId INT,
        filterId INT,
        customerDeviceId INT,
        filterChangeDate DATETIME2
    )

    -- Merge is used instead of a plain INSERT so that we can capture
    -- a combination of source and inserted data
    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.filterId, INSERTED.customerDeviceId, SRC.filterChangeDate
            INTO @FilterChangeData
    ;

    INSERT INTO FilterChange (customerId, filterId, customerDeviceId, filterChangeDate)
        SELECT customerId, filterId, customerDeviceId, filterChangeDate 
        FROM @FilterChangeData
END

Given the following @dt source data:

customerId	deviceId	deviceBuyDate	devicePrice	filterId	filterChangeDate
11	111	2023-01-01	111.1100	1111	2023-02-01
22	222	2023-01-02	222.2200	2222	2023-02-02
33	333	2023-01-03	333.3300	3333	2023-02-03
11	222	2023-01-04	333.3300	1111	2023-02-04

The following is inserted into CustomerDevice:

customerDeviceId	customerId	deviceId	deviceBuyDate	devicePrice
1	11	111	2023-01-01	111.1100
2	22	222	2023-01-02	222.2200
3	33	333	2023-01-03	333.3300
4	11	222	2023-01-04	333.3300

The following is inserted into FilterChange:

customerId	filterId	customerDeviceId	filterChangeDate
11	1111	1	2023-02-01
22	2222	2	2023-02-02
33	3333	3	2023-02-03
11	1111	4	2023-02-04

See
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