Stack Overflow
Stack Overflow

Questions tagged [output-clause]

32 questions
65
votes
8 answers

@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select…
Seibar
  • 68,705
  • 38
  • 88
  • 99
7
votes
1 answer

Output to Temporary Table in SQL Server 2005

I am trying to use the OUTPUT clause inside a stored procedure to output to a temporary table the values of an indentity column after an INSERT. CREATE TABLE #Test ( ID INT ) INSERT INTO [TableB] OUTPUT INSERTED.ID #Test SELECT * FROM…
B.M
  • 533
  • 2
  • 8
  • 16
7
votes
3 answers

DELETE ... OUTPUT COUNT(DELETED.*)

I want to know how many rows were removed in a certain DELETE operation. I took the Microsoft example B which is DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621; and tried to modify it to return only the count of…
Alexander
  • 19,906
  • 19
  • 75
  • 162
7
votes
2 answers

SQL Server: Multiple Output Clauses

I have two tables, Table_1 and Table_2. Table_1 has columns PK (autoincrementing int) and Value (nchar(10)). Table_2 has FK (int), Key (nchar(10)) and Value (nchar(10)). That is to say, Table_1 is a table of data and Table_2 is a key-value store…
SuperNES
  • 2,760
  • 9
  • 37
  • 49
6
votes
1 answer

Using OUTPUT INTO with from_table_name in an INSERT statement

Microsoft's OUTPUT Clause documentation says that you are allowed to use from_table_name in the OUTPUT clause's column name. There are two examples of this: Using OUTPUT INTO with from_table_name in an UPDATE statement Using OUTPUT INTO with…
ANisus
  • 74,460
  • 29
  • 162
  • 158
6
votes
1 answer

Output Clause: The multi-part identifier could not be bound

I am running in to the dreaded "The multi-part identifier could not be bound" error on a stored procedure I am currently working on. I have a few questions in regards to the query below. Why am I getting this error? Why would this error occur on…
Phillip Benages
  • 661
  • 2
  • 9
  • 20
6
votes
2 answers

SQL Server OUTPUT clause

I am a little stuck with why I can not seem to get the 'new identity' of the inserted row with the statement below. SCOPE_IDENTITY() just returns null. declare @WorkRequestQueueID int declare @LastException nvarchar(MAX) set @WorkRequestQueueID =…
Microsoft Developer
  • 1,919
  • 1
  • 20
  • 27
6
votes
1 answer

How to use output clause in SQL to output insert, update, delete results into new temporary table?

Currently, I'm trying to perform an update in SQL Server (but it could be any DML statement that supports the output clause), and I'd like to put the output into a local temp table, like so: update dbo.MyTable set MyField = 30 output …
casperOne
  • 73,706
  • 19
  • 184
  • 253
5
votes
1 answer

output clause VS triggers

On our database, most tables have a dbupddate field which indicates the datetime of the last INSERT or UPDATE applied at the row. In order to avoid this field having an erroneous value, there exist triggers (sometimes AFTER, sometimes INSTEAD OF)…
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
5
votes
2 answers

Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause

I have a table with two columns: CREATE TABLE MyTable( Id int IDENTITY(1,1) NOT NULL, Name nvarchar(100) NOT NULL); I want to duplicate the data using SELECT INSERT statement: INSERT INTO MyTable (Name) SELECT Name FROM MyTable and here is the…
HuBeZa
  • 4,715
  • 3
  • 36
  • 58
4
votes
6 answers

Output columns not in destination table?

SUMMARY: I need to use an OUTPUT clause on an INSERT statement to return columns which don't exist on the table into which I'm inserting. If I can avoid it, I don't want to add columns to the table to which I'm inserting. DETAILS: My…
lance
  • 16,092
  • 19
  • 77
  • 136
2
votes
2 answers

Get Identity of destination table when using **DELETE FROM ... OUTPUT ... INTO**

I use bellow code to archive old data in ArchiveTable and delete archived data from SourceTable DELETE FROM SourceTable OUTPUT DELETED.[ID], DELETED.[Code], DELETED.[Title] INTO ArchiveTable([OldID], [Code], [Title]) WHERE…
Fred
  • 3,365
  • 4
  • 36
  • 57
2
votes
1 answer

SQL Server INSERT statement's OUTPUT clause not returning identity values

On a SQL Server 2008 R2 database server, I attempt to insert data into a table, but the INSERT statement's OUTPUT clause returns only zeros for the table's Identity column. The INSERT statement below is part of a larger script with many statements…
Zarepheth
  • 2,465
  • 2
  • 32
  • 49
2
votes
2 answers

How do I insert multiple columns into a table using OUTPUT in SQL Server?

Scroll down to the OUTPUT part. It's giving me a red underline. I want to insert into the InsertedCreditDebitAdjustmentIDs table, the ID inserted into @CreditDebitAdjustment along with the InvoiceNum. How do I go about doing this? DECLARE…
JJ.
  • 9,580
  • 37
  • 116
  • 189
2
votes
1 answer

Best way to obtain new uniqueidentifier from an SQL insert statement while still determining success or failure of insertion?

I'm starting to work with uniqueidentifiers, and I'm encountering an unexpected issue. First of all, where I'd typically use SCOPE_IDENTITY(), this is no longer possible with a uniqueidentifier, even though in concept it still involves an…
Triynko
  • 18,766
  • 21
  • 107
  • 173
1
2 3