---- Quote :-------------------------------------------------------------------------------------------------------------------------

I completely agree that the implementation of the OUTPUT clause for INSERT is... shall we say, disappointing.

And you're correct that there may be obstacles to modifying prod...

• This reply was modified 6 years, 5 months ago by ben.brugman.
• This reply was modified 6 years, 5 months ago by ben.brugman.

'Activity' of 'Activities' is probably the correct word.

But not sure.

Ben

Thank you schleep,

One workaround is to add the OldId column to the new table, and include it in your INSERT statement. It’ll then be available to the OUTPUT..INTO.

In general you...

Hello Scott, David, Thom and other readers.

Optimistic as I was I 'thought' that the OUTPUT clause in the INSERT (and UPDATE) statement was the solution to get the OLD identity...

• This reply was modified 6 years, 5 months ago by ben.brugman. Reason: Formatting

The thread

using-the-output-clause-in-a-insert-statement

Partially describes the given problem. How to resolve the identities when data gets transported from one (SQL-server) system to another SQL-server system. And There is an identity involved, which...

-- Found the formatting smily smily smily.
-- Example
-- Ben
-- 20190401
--
-- 
drop table A; 
drop table #tempA ; 
drop table resolveA
----------------–
-- Set up a table A
----------------–
Create table A...

• This reply was modified 6 years, 5 months ago by ben.brugman. Reason: Formatting

Hello Tom,

In the temp version of the table, you see an id, this does not get inserted into the new table.

In the example if you run : SELECT * FROM...

• This reply was modified 6 years, 5 months ago by ben.brugman.

Oh, then you want:

<span class="pln">OUTPUT inserted</span><span class="pun">.</span><span class="pln">IDColumn </span><span class="kwd">AS</span><span class="pln"> NewID</span><span class="pun">,</span><span class="pln">
       deleted</span><span class="pun">.</span><span class="pln">IDColumn...

• This reply was modified 6 years, 5 months ago by ben.brugman. Reason: Formatting

/*
Thanks for your anwser.
What I want is the 'OLD' and the NEW identity.
The OLD identity is the identity in a #tempA file.
Because a new Identity is inserted,...

• This reply was modified 6 years, 5 months ago by ben.brugman.
• This reply was modified 6 years, 5 months ago by ben.brugman.
• This reply was modified 6 years, 5 months ago by ben.brugman. Reason: Formatting :-)

DECLARE @T VARCHAR(300) = 'A_Table_Name' ; DECLARE @C NVARCHAR(4000) = '''--''' SELECT @C = @C + '
,'+COLUMN_NAME FROM information_schema.COLUMNS where TABLE_NAME = @T
DECLARE @s-2 nvarchar(max) = 'WITH...

Not a complete anwser/solution yet, but for readers of this thread and me this might help :

The OUTPUT Clause:
The OUTPUT Clause for UPDATE Statements

See :

https://xkcd.com/327/

This does explain fairly well some of the riscs with your solution.
Ben