https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15
https://www.sqlbook.com/sql-server/output-clause/
https://blog.sqlauthority.com/2017/06/29/sql-server-get-column-names-specific-table/
https://docs.microsoft.com/en-us/azure/architecture/patterns/compensating-transaction
-- Insert 3 rows of data into this table and use the OUTPUT -- clause to return all of the columns for each row we insert INSERT INTO Organisation OUTPUT [Inserted].* VALUES ('Super Co.', 'USA'), ('Great Co.', 'UK'), ('Fab Co.', 'India');
INSERT TABLE-1 VALUES (“X”, “Y”)
UPDATE TABLE-2 SET COLUMN-2A=”New-A”, COLUMN-2B=”New-B” WHERE COLUMN-2C=”value-C”
DELETE TABLE-3 WHERE COLUMN-3A=”Val1” AND COLUMN-3G=”Val2”
INSERT TABLE-1
OUTPUT INSERTED.* INTO @Table1-Var -- Table1-Var will have rows inserted along with their IDs
VALUES (“X”, “Y”)
UPDATE TABLE-2 SET COLUMN-2A=”New-A”, COLUMN-2B=”New-B” WHERE COLUMN-2C=”value-C”
OUTPUT DELETED.* INTO @Table2-Var -- All updated rows with old values and IDs
DELETE TABLE-3
OUTPUT DELETED.* INTO @Table3-Var -- All deleted rows with IDs
WHERE COLUMN-3A=”Val1” AND COLUMN-3G=”Val2”
-- Record compensating transaction for INSERT using
record-compensating-txn(<process-instanceId>, <process-block-id>, “TABLE-1”, “DELETE”, @Table1-Var)
-- Record compensating transaction for UPDATE using
record-compensating-txn(<process-instanceId>, <process-block-id>, “TABLE-2”, “UPDATE”, @Table2-Var)
-- Record compensating transaction for DELETE using
record-compensating-txn(<process-instanceId>, <process-block-id>, “TABLE-3”, “INSERT”, @Table3-Var)
