Use Trigger To Avoid Duplicates?

The situation is actually less transparent than it appears: the recordsets aren't too large, and only seven attributes make the entry duplicate or not.  (The rest of that data in the record may drift; it's difficult to say...)  In preparation to make things run even faster, the table is (or will soon be) carved back to a single 30-day group of records (less than 2,000) rather than the entire last two years (over 1,000,000!) or records.

Then we have to add another wrinkle: the accounting data is extracted via a view: the Index feature is surely the least expensive method - and by far the most efficient - but the idea behind this procedure is to make it totally self-sufficient, even though it tends to be a little more expensive.  (Beleive me, I had already thought of that.  It was the simpler solution, but it requires repeat visits on site, thus causing the Customer to beleive that he's being taken advantage of...)  So coming in to the office to perform a two-phase "eradication" of duplicate records on a regular basis might seem less efficient to the Customer.  (But hey, I'm okay with the additional income...)

So it comes down to the need for a trigger to intercept a record during INSERT, determine (by checking the five-to-seven columns) if it *is* a duplicate (regardless of what the additional columns contain), and <somehow> mark the record as a duplicate, probably by overloading a column, otherwise leaving it alone, and letting it INSERT as it was.

But how to approach it?  I almost always get burnt because I'm too ignorant to know how to reference the INSERT data vs. the existing data.  And - of course - there are almost no (known) notes to help me figure this out.

Thanks for your information, though - you're incredibly sharp!  And I really appreciate that.

Hope this message finds you well,

-LongFist

Waitaseond!  When I read:

>>Also:
>>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvrefindexkeyspropertypage.asp
>>
>>By creating an index, you can delete the duplicate rows with the option:
>>Ignore duplicate rows   Deletes duplicate rows from the index as it is being created
>>
>>You can copy the data to a temp table, with your input/original data source for
>>checking across you >>range create an index, automatically delete the rows-
>>-- hey presto -- nice quick solution.
>>(Generally the index creation to delete dups is the fastest mechanism to remove
>>dups in a billing run -- I've seen this done at many sites this way)

I thought you meant perform the rollover of original data to/from the Temp dta each time - but as I read the second article, I began to SEE THE LIGHT.

Shoot, you may have answered this perfectly.  Let me try it on site, and see if the monster gronks.  When the twonky stops showing duplicate records, you get the points.  (And well-earned points they are, too!  Thank you!!!)

Thanks again --- sorry I misread it at first, but I was scanning more than reading to determine if I had a solution or not - you'd think I'd pay more attention, right?

Again, I hope this message finds you well,

-LongFist

Okay - I'll admit it - I'm still new to all the new techniques we have available to us since SQL Server 6.0 (and since my employers generally love my code, they almost never let me play with the SQL Server for any length of time...) - so i'm not sure how I'd spread the dupe-check functionality across to the TempDB.  Or is that what you are saying?

I'm sorry if I strike you as being an ignorant jerk - I admit that I *am* ignorant.  But sometimes the complexity of the simplest tasks escapes me.  Mind you, in VB/VC++ and .Net, the world spins a lot differently, and I'm so much more at home there.

Thanks again - I'm eager to apply what I've learned on the site: I think you've really hit on the solution!

Did I mention Thank You?  For your time and trouble, that is...

-LongFist