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
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:
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