Here a demonstration of the principle. (NOT WORKING).

The idea is if you have 'some' constraint where you can make the offending rows visible, you can use a view to show...

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

Timing without the index was 7.54 seconds

Timing with the index was 7.37333 seconds

1 000 000 rows, cache cleared. Tested in SSMS.

In both cases the table is scanned and the index...

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

I tried the following.

Create a persistent view which only contains the rows with more than four occurences.

Then double that by cross joining it with a table with two rows.

And then...

You can click the “insert/edit code” button and paste the code in the item that pops up.

I do not see an insert/edit code button. (Chrome...

---- 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 5 years, 7 months ago by  ben.brugman.
• This reply was modified 5 years, 7 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 5 years, 7 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 5 years, 7 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 5 years, 7 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 5 years, 7 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 5 years, 7 months ago by  ben.brugman.
• This reply was modified 5 years, 7 months ago by  ben.brugman.
• This reply was modified 5 years, 7 months ago by  ben.brugman. Reason: Formatting :-)