Questions tagged [output-clause]
The output-clause tag has no summary.
20 questions
11
votes
2
answers
586
views
Trigger on cascading update target doesn't fire when the OUTPUT clause is used
I have 2 tables with the cascade delete rule - [dbo].[Invoices] and [dbo].[InvoiceRows]:
CREATE TABLE [dbo].[Invoices]
(
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
--other columns
...
- 113
0
votes
2
answers
302
views
why sp_ExecuteSql output parameter returns null from linked server when procedure shows correct value in results panel
I am trying to get SQL version from a linked server and save it in a variable, but the query runs fine and showed me the result but when I review the output variable is Null.
DECLARE @cmd NVARCHAR(MAX)...
8
votes
2
answers
961
views
OUTPUT clause returning 0 for newly inserted identity value due to INSTEAD OF trigger
Consider the following minimal, complete, and verifiable example code (see dbfiddle here):
CREATE TABLE [dbo].[test]
(
[i] bigint NOT NULL
identity(1,1)
PRIMARY KEY CLUSTERED
...
- 71.1k
0
votes
1
answer
725
views
Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows
1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values:
[a,b,Null], [c,d,Null], [e,f,Null]
2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
- 21
0
votes
1
answer
109
views
Should I worry about a serial update execution plan?
For a mature .Net project I support we're currently exploring options to remove Entity Framework Core from the project while still maintaining some of the EF functionality we rely on.
At the top of ...
- 103
0
votes
1
answer
949
views
Outputting source column in MERGE OUTPUT clause
I have a relatively simple question with an implied answer, but not an explicit one. Here's the background.
Here are the 3 schemas I'm working with:
--Source Data:
ProjectID, ProjectName, CompanyName
...
- 117
7
votes
1
answer
572
views
OUTPUT clause with window functions
Is there an undocumented restriction in the OUTPUT clause, or is this a bug?
Given the following table:
CREATE TABLE t1 (SomeId int, flag bit, value int);
I would like to use a calculated value in an ...
- 18.1k
-4
votes
1
answer
136
views
Finding the errors in SQL and interpreting what the SQL codes mean
Find all 5 errors in the SQL script below and explain what the script is trying to do
select
A.BIZ_DT,
A.ACTL_PARENT_SVC_NUM,
A.DIRCTN_IND,
A.BUS_STOP_CD,
A.BUS_STOP_ARRVL_TM,
A.OPR_ID_NUM,
A.REG_NUM,
...
2
votes
2
answers
6k
views
Can I use OUTPUT from an UPDATE in an INSERT?
I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for ...
- 205
2
votes
1
answer
285
views
SQL Server - Update with Output not working in Parallel environment
I know that the output parameter does not guarantees parallelism logic.
And the logic I want to implement is failing in a multi thread environment.
To test this, just open 2 windows and execute them ...
- 221
2
votes
1
answer
5k
views
How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?
How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.
...
- 1,955
3
votes
1
answer
10k
views
Using the result set of a select query as the input of another select
I need to make three select queries over three different tables, using the outputs of each select query, the catch is each one gives multiple results. Here is how I do it.
Select "Title", "Num" from "...
- 33
34
votes
2
answers
34k
views
Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)
I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each ...
- 593
0
votes
1
answer
429
views
T-SQL insert with output expression [closed]
insert Table1
OUTPUT inserted.ID, s.[Id]
INTO @Inserted
select
s.[Field1],
s.[Field2],
from
@TableVariable s
why is s.[Id] marked by red line ? Is it non-correct syntax to save value in @Inserted ...
- 241
2
votes
1
answer
887
views
How do I load data from a single table to multiple tables & keep referential integrity [closed]
Using SQL Server 2008R2.
I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the ...
- 183