insert with output clause
-
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; -
In SQL Server SQL there is no output clause for select. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
-
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.
-
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
-
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]
? -
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.
- schleep - Thursday, January 10, 2019 4:08 AM
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) - N_Muller wrote:
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.
Yep
declare @master table(id int identity(1,1), name varchar(10));
declare @master_values table(id int identity(1,1), name varchar(10), value int);
insert @master(name) values ('kim'), ('jack');
insert @master_values(name, value)
select m.name, l.value
from
@master m
join
(values (1, 10), (2, 15 )) as l (id, value) on l.id = m.id;
declare @values table (id int, value int, dt datetime2(2) default(getdate()));
declare @lastupdate table (name varchar(10), value int, dt datetime2(2));
merge
@values as t
using
@master_values as src
on
1=0
when not matched then
insert values(src.id, src.value, getdate())
output
inserted.id, src.name, inserted.value;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hello,
Your examples are really apt.
I also face problem in the creation of columns sometimes,
A copy of an existing table can also be created using the CREATE TABLE really easily.
The new table gets the same column definitions and even all columns or specific columns can be selected.
If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply