R T on 09 May 2024 14:24:27
The OUTPUT statement is a commonly-used function in the world of databases and T-SQL and is integral to a variety of data operations. Not being able to return the OUTPUT of a script severely hampers the ability to perform numerous data operations that have dependencies on consuming newly created keys and adds a barrier to Fabric adoption.
EXAMPLE:
You are loading multiple files from multiple clients. You want to ensure data quality and be able to clean up the data warehouse if bad data gets loaded. You use a standard DB practice of creating a logging table and inserting a record so you have a unique ID that you can then assign to all upserted records. Your normal process would be:
- Insert log record.
- Output new log record key.
- Include log record key in upsert scripts for tables.
Without an OUTPUT statement in Fabric, you can't perform item #2 as there could be numerous jobs inserting to the same table concurrently.
MS Document Link: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16