Sunday, March 14, 2021

Output Clause - MSSQL - INSERTED, DELETED - Microsoft Azure Database

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)

No comments:

Post a Comment

Azure - Pipeline - Add Approver for Stage

https://learn.microsoft.com/en-us/azure/devops/pipelines/process/approvals?view=azure-devops&tabs=check-pass