SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


insert with output clause


insert with output clause

Author
Message
N_Muller
N_Muller
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3291 Visits: 496
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
Joe Torre
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5873 Visits: 1112
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
N_Muller
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3291 Visits: 496

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
Sue_H
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59369 Visits: 12859


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
Ahmad 56
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 2
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 @contacts
SELECT [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] Keyboard and Mouse price in Pakistan

INSERT [Mappings] ( [TargetID], [SourceGUID] )
SELECT [TargetID], [GUID]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search