August 17, 2017 at 2:45 pm
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;
August 17, 2017 at 3:04 pm
In SQL Server SQL there is no output clause for select. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
August 17, 2017 at 7:25 pm
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.
August 17, 2017 at 7:56 pm
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
August 22, 2017 at 9:38 am
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]
?
January 10, 2019 at 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.
February 13, 2019 at 12:25 pm
schleep - Thursday, January 10, 2019 4:08 AMI 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2019 at 6:47 am
This was removed by the editor as SPAM
February 15, 2019 at 6:48 am
This was removed by the editor as SPAM
August 5, 2019 at 10:01 am
This was removed by the editor as SPAM
August 5, 2019 at 10:04 am
This was removed by the editor as SPAM
August 17, 2019 at 5:02 am
This was removed by the editor as SPAM
September 2, 2019 at 5:13 am
This was removed by the editor as SPAM
September 2, 2019 at 5:15 am
This was removed by the editor as SPAM
September 2, 2019 at 5:16 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply