UPDATE table A, then INSERT new values into table B (with history log of what was updated)

Question

I am looking for a way to updated some values on specified SQL tables (tr__Transaction) and insert some values to history table (tr_History) with description what was updated (string value) AT THE SAME TIME (in a single query) WITHOUT USING LOOP and TRIGGERS.

This is not database designed by me but it's existing one I am working on. For example I am updating existing records in (1,3,5) set with new value in tr_ShippingMethodId column.

3 rows are updated and now I want to insert into the tr_History table new records corresponding to my sample query.

This is my sample code:

DECLARE @tr__Transaction TABLE (tr_Id int, tr_ShippingMethodId int) -- table with transactions (online sale)

INSERT INTO @tr__Transaction (tr_Id, tr_ShippingMethodId) 
VALUES (1, 2), (2, 2), (3, 9), (4, 8), (5, 0)

DECLARE @tr_History TABLE 
                    (
                        th_Id INT, 
                        th_TransId INT, 
                        th_Time DATETIME, 
                        th_Entry NVARCHAR(255)
                    ) -- table with history log (what changed made on transaction)

DECLARE @TrIdsToUpdate TABLE (trIdToUpdate int) -- table with ids to update

INSERT INTO @TrIdsToUpdate VALUES (1, 3, 5)

UPDATE @tr__Transaction 
SET tr_ShippingMethodId = 99 
WHERE tr_Id IN (SELECT trIdToUpdate FROM @TrIdsToUpdate)

INSERT INTO @tr_History (th_Id, th_TransId, th_Time, th_Entry) 
VALUES (spGetId(tr_History), 1, GETDATE(), 'Record 1 changed shipping method from 2 to 99')

INSERT INTO @tr_History (th_Id, th_TransId, th_Time, th_Entry) 
VALUES (spGetId(tr_History), 3, GETDATE(), 'Record 3 changed shipping method from 9 to 99')

INSERT INTO @tr_History (th_Id, th_TransId, th_Time, th_Entry) 
VALUES (spGetId(tr_History), 5, GETDATE(), 'Record 5 changed shipping method from 0 to 99')

In this query I had to use spGetId(tr_History) stored procedure which returns a free ID to insert (this is how it is designed originally).

I also used 3 INSERT commands to demonstrate what I want to achieve but using @TrIdsToUpdate table with single INSERT.

Any suggestion how to do it?

Answer 1

You can use the OUTPUT clause.

DECLARE @tr__Transaction TABLE (tr_Id int PRIMARY KEY, tr_ShippingMethodId int); -- table with transactions (online sale)

INSERT INTO @tr__Transaction (tr_Id, tr_ShippingMethodId) 
VALUES (1, 2), (2, 2), (3, 9), (4, 8), (5, 0);

DECLARE @tr_History TABLE 
                    (
                        th_Id INT IDENTITY PRIMARY KEY, 
                        th_TransId INT, 
                        th_Time DATETIME, 
                        th_Entry NVARCHAR(255)
                    ); -- table with history log (what changed made on transaction)

DECLARE @TrIdsToUpdate TABLE (trIdToUpdate int PRIMARY KEY); -- table with ids to update

INSERT INTO @TrIdsToUpdate VALUES (1), (3), (5);

UPDATE @tr__Transaction
SET tr_ShippingMethodId = 99
  OUTPUT
    inserted.tr_ID,
    GETDATE(),
    CONCAT('Record ', inserted.tr_ID, ' changed shipping method from ', deleted.tr_ShippingMethodId, ' to ', inserted.tr_ShippingMethodId )
  INTO @tr_History
    (th_TransId, th_Time, th_Entry)
WHERE tr_Id IN (SELECT u.trIdToUpdate FROM @TrIdsToUpdate u);

I have no idea what your spGetId function is doing, but it's probably a bad idea and you should just use an IDENTITY or SEQUENCE column.

It would probably be wise to put primary keys on the table variables also, as shown, for performance reasons.