Tag: SQL301

Last time, we discussed three methods for inserting data into a destination table that has a recursive relationship.  In this article we’re going to cover how to insert data into that table without vi0lating the foreign key constraint by using a WHILE EXISTS loop. If you want to follow along with the examples, you’re going…

Earlier this week the question came  up on how can we insert data into a table that has a hierarchical data structure.  If you’re not familiar with a hierarchical data structures, here’s a quick explanation. Consider the staging table to the right.  This table is used to load new accounts into our system.  Each account…

This is the last article for my upcoming design patterns session. Today we’re going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I’m wrong for not understanding cursors, let me say this: there is a time and…

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…

Last time, I covered the EXIST Method.  This time around I wanted to show you how to pull out values from XML stored in SQL.  Let’s start with what should have been a simple example.  I wanted to pull the first and last name for a job applicant along with their resume in XML format….