Skip to content

shannonlowder.com

Menu
  • About
  • Biml Interrogator Demo
  • Latest Posts
Menu

Tag: SQL301

Loading Hierarchical Data Using a WHILE EXISTS

Posted on February 6, 2012February 10, 2012 by slowder

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…

Continue reading

Loading Hierarchical Data

Posted on February 2, 2012February 7, 2012 by slowder

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…

Continue reading

Improve Performance by Replacing Cursors

Posted on January 25, 2012February 1, 2012 by slowder

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…

Continue reading

Upserts

Posted on January 23, 2012February 1, 2012 by slowder

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…

Continue reading

De-duping by CTE

Posted on January 18, 2012 by slowder

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…

Continue reading

De-duping by Self join

Posted on January 11, 2012January 17, 2012 by slowder

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…

Continue reading

De-duping by temp table

Posted on January 9, 2012January 17, 2012 by slowder

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,…

Continue reading

Manually Deleting Stinks!

Posted on January 4, 2012January 4, 2012 by slowder

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…

Continue reading

Design Patterns Session

Posted on January 2, 2012January 25, 2012 by slowder

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…

Continue reading

SQL301–XML VALUE Method

Posted on March 7, 2011February 28, 2011 by slowder

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

Continue reading
  • 1
  • 2
  • 3
  • 4
  • Next
  • Career Development
  • Data Engineering
  • Data Science
  • Infrastructure
  • Microsoft SQL
  • Modern Data Estate
  • Personal
  • Random Technology
© 2023 shannonlowder.com | Powered by Minimalist Blog WordPress Theme