OUTPUT CLAUSE

 OUTPUT returns a result set of the rows inserted, updated or deleted.


Delete rows and return the rows deleted.

DELETE <schema>.<table_name>

OUTPUT DELETED.*

OPTIONAL FROM, JOIN, WHERE, etc.



MERGE

You can access the $action column in the OUTPUT. It is an NVARCHAR(10) that returns 'INSERT', 'UPDATE', or 'DELETE' for each row of the OUTPUT.


You can use WITH (READPAST) on UPDATE and DELETE statements when multiple processes can perform a destructive read.

INSERT Example

INSERT <table_name> 

(<column_list>) 

OUTPUT INSERTED.<colname(s)> INTO <table_name>

VALUES (<value_list>)


UPDATE Example

UPDATE <table_name>

SET <colN> = <value>

OUTPUT

INSERTED.<colname>, DELETED.<colname>

INTO <table_name>

OPTIONAL FROM, WHERE, etc.


Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more