So after the above comments, it's now a giant CASE statement.

So I got a prototype going as a computed column calling a scalar function, and the perf hit appears negligible.

Further investigation underway.

Coming from you, that's a... shall we say, less than ringing endorsement of the current implementation.

Here's the bones of the table, minus the meat columns - and believe it or...

PRINT, IF APP_NAME() LIKE "%QUERY%" BEGIN...END, DECLARE @Debug variable

I remember being SOOO excited when SQL2K came with a debugger... and the crushing disappointment at its unmitigated clunkiness.

If you need to track the date of an insert, and the table doesn't already have a field from which that can be derived, that's a case for Insert trigger.

As...

Well, you asked specifically how it could be achieved using constraint violations, so that's what I came up with.

IMHO, it's clumsy at best.  And,  you must hard code either the...

I suppose you could achieve this using TRY-CATCH. Try to Insert it. If the insert fails on a violation of that constraint, increment the visit count for the user.

I personally...

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 tables.

Another workaround I've...

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.

You can then decide if...

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...

That's pretty impressive Alan, and applied to my real-world data, the results look something like expected.

Thank you very much.

Ah sorry, I thought thew WITH(DISTRIBUTION = xxx) was a required part of the tools for the analysis.

I'll have a go at this, thanks!

Yup, sounds like what I want to do. Except in SQL Server 2008. I don't have access to DW.

Something like

try
{
Get-WmiObject win32_Service -ComputerName $serverList | where {$_.DisplayName -match "SQL Server"} | select SystemName, DisplayName, Name, State, Status, StartMode, StartName |
ConvertTo-Html -Property SystemName,Name,State,Status,StartMode,StartName -Head $Header...