Output columns not in destination table?

Question

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 FinishedDocument table has only one column. This is the table into which I'm inserting.

FinishedDocument
-- DocumentID

My Document table has two columns. This is the table from which I need to return data.

Document
-- DocumentID
-- Description

The following inserts one row into FinishedDocument. Its OUTPUT clause returns the DocumentID which was inserted. This works, but it doesn't give me the Description of the inserted document.

INSERT INTO FinishedDocument
OUTPUT INSERTED.DocumentID
SELECT DocumentID
FROM Document
WHERE DocumentID = @DocumentID

I need to return from the Document table both the DocumentID and the Description of the matching document from the INSERT.

What syntax do I need to pull this off? I'm thinking it's possible only with the one INSERT statement, by tweaking the OUTPUT clause (in a way I clearly don't understand)?

Is there a smarter way that doesn't resemble the path I'm going down here?

EDIT: SQL Server 2005

Am I crazy or did you simplify this so that the real problem isn;t apparent? You don't need to output the documentid, it's already stored in a variable. — HLGEM, May 14 '10 at 19:26

score 3 · Accepted Answer · answered May 14 '10 at 17:55

3

Look at Example A:

DECLARE @temp TABLE (DocumentID int)

INSERT INTO FinishedDocument 
    OUTPUT INSERTED.DocumentID 
    INTO @temp
SELECT DocumentID 
FROM Document 
WHERE DocumentID = @DocumentID 

SELECT Document.DocumentId, Document.Description
FROM @temp AS t
INNER JOIN Document 
    ON t.DocumentID = Document.DocumentID

answered May 14 '10 at 17:55

Cade Roux

88,164
40
182
265

The page you linked states that "the OUTPUT INTO clause is not supported in INSERT statements that contain a clause". Turns out that my real world query falls victim to that. – lance May 14 '10 at 18:32
Not quite efficient, but seems to be the only way to do the job. – Guillermo Gutiérrez Feb 04 '13 at 20:38

score 1 · Answer 2 · answered May 14 '10 at 16:16

1

I wonder if you can backdoor it like this (should it concern me that I'd actually consider doing it this way?):

;WITH r (DocumentID)
     AS (INSERT INTO FinishedDocument
         OUTPUT INSERTED.DocumentID
         SELECT DocumentID
           FROM Document
          WHERE DocumentID = @DocumentID)
 SELECT d.DocumentID, d.DocumentName
   FROM Document d
   JOIN r
     ON d.DocumentID = r.DocumentID

Your insert still uses the OUTPUT clause, but as an inline table that then links up to the Document to get your required info. Though, for some reason, I can't escape the feeling that this bastardizes the WITH clause hopelessly...

answered May 14 '10 at 16:16

SqlRyan

33,116
33
114
199

1

I am led to believe that the CTE can only have a SELECT statement inside it.... so as good as this tip looks, I doubt it'll work. – marc_s May 14 '10 at 17:10
1

@marc_s and David: I was afraid that would happen, and I didn't have SQL handy to test it. Plus the statement just makes me uncomfortable to being with, having that insert inside the CTE... I'll come up with something else and update. – SqlRyan May 14 '10 at 21:04

score 1 · Answer 3 · answered May 14 '10 at 16:16

The OUTPUT clause can contain any of the fields that it operates on, e.g. in your case: any of the fields you're inserting data into. That means: any of the columns from the FinishedDocument table.

The OUTPUT clause however cannot join or grab data from other tables.

score 0 · Answer 4 · answered May 14 '10 at 16:17

0

Wrap the insert into a CTE, then on the select join the output back to your document table.

answered May 14 '10 at 16:17

NotMe

87,343
27
171
245

score 0 · Answer 5 · answered May 14 '10 at 16:36

0

In 2008 you can do it with a MERGE statement. Maybe you could consider upgrading :)

MERGE INTO FinishedDocument
USING Document d ON 1=0
WHEN NOT MATCHED AND d.DocumentID = @DocumentID THEN
INSERT (DocumentID) VALUES (@DocumentID)
OUTPUT d.DocumentID, d.Description;

I suggest you check the execution plan before you try this.

Is it worth it? You are only inserting from a variable so having two statements, one to select and one to insert isn't going to cost you anything extra. Use two statements.

answered May 14 '10 at 16:36

nvogel

24,981
1
44
82

My real world is a bit more complex than my question. In my real world, this is in a stored procedure which multiple users will be calling at the same time, and @DocumentID is actually a condition which will pass until the INSERT occurs. So, I'm afraid that a SELECT-then-INSERT could result in two users getting back the same document (because UserB called the sproc and SELECTed RowX after UserA selected RowX (but before UserA INSERTed, which would prevent UserB from getting the same row). – lance May 14 '10 at 16:55
Do it the other way round. Output the ID from the INSERT statement and then SELECT the description afterwards. – nvogel May 14 '10 at 17:52
When I output the ID -- into what do I store it (for later use in the SELECT query)? I tried storing it into a variable, but I couldn't figure out the syntax, and "the OUTPUT INTO clause is not supported in INSERT statements that contain a clause" (http://msdn.microsoft.com/en-us/library/ms177564.aspx), so I can't use a temp table? – lance May 14 '10 at 18:27
With regard to reasons not to use the `MERGE` statement in SQL Server, please see Aaron Bertrand's great article [Use Caution with SQL Server's MERGE Statement](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement). – Reversed Engineer Jan 18 '22 at 11:19

score 0 · Answer 6 · answered May 14 '10 at 18:53

0

How about something like this?

create table FinishedDocument (
    DocumentId int
)

create table Document (
    DocumentId int,
    Description nvarchar(100)
)

create table #tmpDoc (
    DocumentId int
)

insert into Document
    (DocumentId, Description)
    values
    (1, 'Test')

insert into FinishedDocument
    (DocumentId)
    output Inserted.DocumentId into #tmpDoc 
    select D.DocumentId
        from Document D
        where D.DocumentId = 1

select D.DocumentId, D.Description
    from #tmpDoc t
        inner join Document D
            on t.DocumentId = D.DocumentId

drop table #tmpDoc      
drop table FinishedDocument
drop table Document

answered May 14 '10 at 18:53

Joe Stefanelli

132,803
19
237
235

http://msdn.microsoft.com/en-us/library/ms177564.aspx states that "the OUTPUT INTO clause is not supported in INSERT statements that contain a clause". Turns out that my real world query falls victim to that. :( – lance May 14 '10 at 18:57
Could you select your into a temp table and use that for the insert? – Joe Stefanelli May 14 '10 at 19:31

Output columns not in destination table?

6 Answers6