insert with output clause

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    I have an insert with an output clause where one of the outputted columns is not in the inserted list, but in the joined table.


    declare @master table ( ID int identity(1,1), Name varchar(10) )
    declare @Values table ( ID int, Value int, DT datetime2(2) default(getdate()) )
    declare @LastUpdate table ( Name varchar(10), Value int, DT datetime2(2) )

    insert @master ( Name ) values ( 'Kim' ), ( 'Jack' )

    insert @Values ( ID, Value )output M.Name, inserted.Value, inserted.DT into @LastUpdate ( Name, Value, DT )
    select M.ID, L.VALUE
    from @master M inner join
    (
    values ( 1, 10 ),( 2, 15 )
    ) AS L ( ID, VALUE ) on
    L.ID = M.ID

    The code above gives me the following error "The multi-part identifier "M.Name" could not be bound".  There is an example in MSDN for output clause in a delete statement that doesn't seem to give the same error (I'm assuming the example is correct).  What's wrong with my query? Any help is appreciated.


    DELETE Production.ProductProductPhoto 
    OUTPUT DELETED.ProductID, 
           p.Name, 
           p.ProductModelID, 
           DELETED.ProductPhotoID 
        INTO @MyTableVar 
    FROM Production.ProductProductPhoto AS ph 
    JOIN Production.Product as p  
        ON ph.ProductID = p.ProductID  
        WHERE p.ProductModelID BETWEEN 120 and 130; 


  • Joe Torre

    SSChampion

    Points: 10225

    In SQL Server SQL there is no output clause for select. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    I can always change a select-insert into a merge. There's never going to be a "WHEN MATCHED". Must try to see if it solves the problem.

  • Sue_H

    SSC Guru

    Points: 89905

    The examples are correct. Your joins are not really like the examples.
    Look at the example above the delete that is doing the update. To do the same (which you can't - just hypothetically), you would need something like FROM @Values join @master, etc.
    You can't use columns from the select part of an insert. And select into won't work. For an insert, you'd only be able to reference the columns from the inserted table. I've seen some workarounds for similar things with a merge but don't remember any of the details.
    Just curious - is there a reason you don't want to use a history table and triggers? Seems like you are doing the same type of thing and those are fairly common, much simpler.

    Sue

  • Ahmad 56

    Valued Member

    Points: 61

    I even have a saved technique that wishes to insert into 3 exclusive tables, but I want to get the ID generated from the one input and use that to insert into the following table. I'm acquainted with the INSERT-OUTPUT construct, but I'm now not certain how to go approximately the usage of it in this particular case.

    DECLARE @guids TABLE ( [GUID] UNIQUEIDENTIFIER );

    DECLARE @contacts TABLE ( [ContactID] INT, [GUID] UNIQUEIDENTIFIER );

    DECLARE @mappings TABLE ( [TargetID] INT, [GUID] UNIQUEIDENTIFIER );

    INSERT @guids ( [GUID] ) ...

    INSERT [Contacts] ( [FirstName], [LastName], [ModifiedDate] )

    OUTPUT [inserted].[ContactID], g.[GUID]

    Β INTO @contactsSELECT [First_Name], [Last_Name], GETDATE()

    FROM [SourceTable] s

    JOIN @guids g ON s.[GUID] = g.[GUID]

    INSERT [TargetTable] ( [ContactID], [License], [CreatedDate], [ModifiedDate] )

    OUTPUT [inserted].[TargetID], c.[GUID]

    INTO @mappings

    SELECT c.[ContactID], [License], [CreatedDate], [CreatedDate]

    FROM [SourceTable] s

    JOIN @contacts c ON s.[GUID] = c.[GUID]


    ?

     

    • This reply was modified 3 months, 3 weeks ago by  Ahmad 56.
    • This reply was modified 3 months, 2 weeks ago by  Steve Jones - SSC Editor. Reason: frormat code
  • schleep

    SSChampion

    Points: 12529

    I ran into this before: while I can't locate the exact reference, I believe only columns in the insert list and identities (ie, columns from the inserted table) can be outputed from an INSERT.

  • sgmunson

    SSC Guru

    Points: 110433

    schleep - Thursday, January 10, 2019 4:08 AM

    I ran into this before: while I can't locate the exact reference, I believe only columns in the insert list and identities (ie, columns from the inserted table) can be outputed from an INSERT.

    That is 100% correct.   I ran into this relatively recently, and was able to update the definition of my temp table to accommodate an additional column so that it would get inserted, and thus be eligible for OUTPUT.

    Steve
    β€Œ(aka sgmunson)
    β€Œ:) πŸ™‚ πŸ™‚
    β€ŒHealth & Nutrition

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • spycamera

    Newbie

    Points: 1

    This reply has been reported for inappropriate content.

    This is a great and inspiring article. We very thankful for this great information.

    Regards, Spy Camera

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply