Menu
I am trying to use a filter on an OUTPUT clause in t-sql.
What I want to do is something like this:
Insert into tbl_1(col1,col2)
Output Inserted.col1 into #tbl_temp
**where col1 > 0**
select col3, col4
from tbl_2
For performance reasons I don't want to use two insert statements.
382
Structured Query Language (SQL) allows filtering data during querying. Using various techniques of filtering in SQL, one can refine the output of queries without having to implement any separate logic or coding. In this article, we will cover below topics related to filtering in SQL: "GROUP BY" clause, "HAVING" clause and "FILTER" modifier.
The filter clause is another tool for filtering in SQL. The filter clause is used to, as the name suggests, filter the the input data to an aggregation function. They differ from WHERE clause in the aspect that they are more flexible than the WHERE clause. Only one WHERE clause can be used at a time in a query.
You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. That is, the application is constantly adding or removing rows from the table. The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application. SQL.
The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
insert into #tbl_temp
select col1
from
(
insert into tbl_1(col1,col2)
output Inserted.col1
select col3, col4
from tbl_2
) as T
where T.col1 > 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
