I don't *think* Group-Object is what I'm looking for: "...a table with one row for each property value and a column that displays the number of items with that value."....

I meant, in Powershell, how can i get the equivalent of

SELECT Table, Col1 = SUM(Col1), Col2 = SUM(Col2)

FROM <$array>

GROUP BY Table

In my example,

Table    Col1    Col2

Table1        4         9

Table2       6        11

I don't see how a trigger helps in this scenario...? If there's no table activity, no trigger is fired, nothing changes...? Or am I standing to close to the tree...

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!