Category: Microsoft SQL

Up to this point in our design patterns sessions we’ve concentrated on removing duplicates from our troubled table.  Now, let’s look into preventing those duplicates from being inserted in the first place.  If you haven’t heard the phrase upsert before, it’s the idea of doing an INSERT and UPDATE in the same statement, that way…

Ok, today we wrap up our design patterns for removing duplicates from tables by turning to common table expressions (CTE’s). When I start talking CTE’s some people begin to cringe in fear. I’ll admit, I still have to refer to a reference when doing something I haven’t done before with a CTE, which is pretty…

Let’s continue the series on getting rid of duplicate data.  Today we’re going to use self joins to get rid of them.  A self join is a join from a table back to itself.  It might seem weird to do that, but by the end of this article you’ll see how it’s useful.  I use…

Last time, we learned that manually removing duplicate rows from a table could be a long and manual process.  Especially when you have as many duplicates as I created in our troubled table.  Today we’re going to look at using temp tables to remove the extra rows.  I gave you the overview to this already,…

Go ahead and set up your TroubledTable by running the Create Troubled Table T-SQl Script.  Now take a look at your duplicate rows. SELECT email, COUNT(*) FROM troubledTable GROUP BY email HAVING COUNT(*) > 1 You’ve got 200 rows that have been duplicated over and over.  Not a pretty sight.  Imagine how long it you…

On February 6, I’m going to tele-present a session on T-SQL design patterns to the Brazil SQL Server Users Group.  During this session I’ll cover three common scenarios you face, that can be solved by using T-SQL: Removing duplicate rows from tables. Create Troubled Table T-SQl Script — This contains the table we’ll use for…