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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 492
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2891 Visits: 775
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 492

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-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33185 Visits: 9455


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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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