Display columns not in destination table?

Question

SUMMARY:
I need to use the OUTPUT clause on INSERT to return columns that do not exist in the table I am inserting into. If I can avoid this, I do not want to add columns to the table I am inserting into.

DETAILS:
There is only one column in my FinishedDocument table. This is the table I'm pasting.

FinishedDocument
- DocumentID

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

Document - DocumentID
- Description

Next insert one line in FinishedDocument. His OUTPUT clause returns the DocumentID that was inserted. This works, but it does not give me a description of the inserted document.

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

I need to return from the table Document and DocumentID and the description of the corresponding document from INSERT.

What syntax do I need to remove? I think this is possible with only one INSERT statement, by setting the OUTPUT clause (somehow I don't understand)?

Is there a smarter way that doesn't look like the path I'm going to here?

EDIT: SQL Server 2005

Answer 1

See 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

Answer 2

, , ? ( , ?):

;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

OUTPUT, , , . , - , , WITH...

Answer 3

OUTPUT , , . : , . : FinishedDocument.

OUTPUT, , .

Answer 4

CTE, join, .

Answer 5

In 2008, you can do this with the MERGE statement. Perhaps you can consider updating :)

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 implementation plan before trying to do this.

Is it worth it? You just paste from a variable, so having two statements, one for selection and one for insertion will not cost you anything extra. Use two operators.

Answer 6

How about something like that?

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