Tag Archives: Transact-SQL

Test Data Builders at SQL Saturday Las Vegas (Sept 12)

At SQL Saturday Las Vegas (Sept. 12, 2015), I’ll be presenting about test data builders. I’ve attached the slide deck and demo files to this post for your convenience. They’re also available directly from the SQL Saturday site, just visit my session summary on the schedule.

Building Your Way to Better Database Testing – Phil Helmer – SQL Saturday LV 2015 #436.zip

You will also have the chance to hear about this topic at SQL Saturday San Diego on September 19.

Even if you aren’t interested in my presentation, you should consider coming out to one or both of these events. You get a day of high quality free training from people who really care about the SQL Server community.

T-SQL Test Automation (part 1 of 4): Get back on course

The series at a glance:

  1. Get back on course
  2. Live in the now, look to the future
  3. Time to focus
  4. Are we there yet?

WARNING: Excessive metaphors and clichés ahead!


Sometimes Often in life, the hardest thing to do is to just get started on something. Pay attention to how many of your thoughts begin with “I should…” Yep, same here. So, my most recent “I should” was “I should really use this blog site since I bothered to pay for a domain name.”

And on that theme, I offer you a plan to do the same… for the code in your SQL Server database.
Continue reading

SoCal Code Camp San Diego 2012 session

If you’re looking for the slides & code files for my SoCal Code Camp San Diego 2012 session on the T-SQL OUTPUT clause, here you go:

Up Your Game with OUTPUT (T-SQL)

Composable DML (OUTPUT clause) inside of a T-SQL Common Table Expression (CTE)

Disclaimer: the following was tested on SQL Server 2008R2.

When I presented my T-SQL OUTPUT topic to the San Diego SQL Server user group last month, a question came up regarding the composable DML use case. For those of you who aren’t familiar with that term, it’s a way in which you can choose to audit only certain rows from a data-changing operation, while using the OUTPUT clause. On its own, the OUTPUT clause returns all rows affected.

So, the basic syntax would look like this:

SET     ...
OUTPUT  inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
INTO    AuditTable

When you run such a statement, a row is inserted into AuditTable for each row updated in MyTable. If you make that a sub-query, however, you can do something like this:

INSERT INTO AuditTable (...)
FROM    (
            UPDATE  MyTable
            SET     ...
            OUTPUT inserted.ID,
                deleted.Amount AS OldAmount,
                inserted.Amount AS NewAmount
        ) AS UpdatedData
WHERE  UpdatedData.OldAmount > 300;

This time around, you only have rows in AuditTable where the value of Amount was 300 before the data changed.

The question which came up is whether you can use that same arrangement, but inside a common table expression. In other words, since OUTPUT returns the equivalent of a SELECT statement, you can place that inside of a CTE, right?

WITH UpdatedData AS (
    UPDATE  MyTable
    SET     ...
    OUTPUT inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
INSERT INTO AuditTable (...)
FROM    UpdatedData
WHERE    UpdatedData.OldAmount > 300;

Well, it turns out you can’t. Unfortunately, the error message isn’t terribly helpful:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'UPDATE'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

So, while you can do some nifty things with OUTPUT and CTEs, it looks like SQL Server doesn’t think that these are 2 great tastes that taste great together. We’ll have to leave that to the peanut butter cups.

T-SQL OUTPUT (San Diego SQL Server User Group)

The slides & files from tonight’s presentation on the T-SQL OUTPUT function are available.


I must say, I do have a particular affinity for language constructs that have come to the rescue on several occasions, and OUTPUT is definitely one of them.

San Diego Code Camp 2011

Can I get a w00t!!!! It’s time for San Diego Code Camp 2011!

What’s that? You haven’t registered? That’s ok, come on down to UCSD and learn your derrière off! If nothing else, show up Sunday morning for a T-SQL double-shot.

I’ll be presenting PIVOT & UNPIVOT at 9am on Sunday and Common Table Expressions at 10:15. If you want to follow along with the demos or want to get your hands on the material after the fact, then you can download the files below. Don’t let the pre-posting of content fool you. There’s lots to learn that isn’t in those files.

SQL Server Data Rotation with PIVOT & UNPIVOT

Common Table Expressions

I look forward to meeting a lot of you at the event and at the dinners!

SoCal Code Camp 2011 Sessions Added

I have just submitted 2 sessions to the Southern California Code Camp, to be held in San Diego on June 25th & 26th.

Should you be interested in either (or both) topics and will be in the area that weekend, please register on the site and vote for them.

If you’re going to be in the area that weekend, I encourage you to attend as the variety of topics is quite broad. You’ll likely find many topics of interest among the submitted session abstracts.

Transact-SQL PIVOT & UNPIVOT redux

That was awesome!! The presentation went well, although it did go 2 minutes over. It’s hard to top the feeling you get when the audience is engaged and asking a lot of great questions.

I’m glad I went with PIVOT & UNPIVOT as my first presentation. By pure luck, a couple of people in the audience had just used PIVOT to solve a reporting problem with it and some others learned about it for the first time. Better yet, at least one of them will be using PIVOT right away in their own work to get rid of the CASE nest that you get if you don’t use these clauses.

Thank you SD SQL PASS chapter and especially all of you who came out on a work night to listen to me go on for 15 17 minutes. It’s ok, we all know you really showed up for the PowerPivot demo. Great job Lakshmi.

As promised, the slide deck and scripts are also available.


This pair of T-SQL commands always seem odd to cover in tandem because they’re not opposites. However, I feel like it’s more confusing to describe them apart from each other. Plus, it’s not like they don’t have any similarities.

Well, as promised, here are the slides and scripts from my presentation: T-SQL PIVOT & UNPIVOT (ZIP).

If you have any questions about the content, drop me a line.