Monthly Archives: April 2011

Excel’s CONCATENATE Function

If you’re not already familiar with this function, I may be about to become your hero. Or, you’ll keep using the Input/Output wizard, some T-SQL, or straight-up SSIS to do this sort of thing and this post won’t matter. Either way, this was new to me a while back and its simplicity and usefulness (at least in our environment) blew my mind at first.

The CONCATENATE function in Excel itself is pretty straightforward; it’s a one-trick pony that does exactly what you think it does: concatenates strings together. In fact, Excel’s description of the function isn’t that much more complicated: “Joins several text strings into one text string.”

Couple notes about CONCATENATE…

The first one has to do with its behavior. CONCATENATE will combine anything in Excel cells, even things that classify as “numbers” that can be used in other arithmetic-based functions. Translated, this means if you have “12” in one cell and “78” in another, using CONCATENATE with those two cells will have a result of “1278.”

The second point is about its source data. Obviously this can be used to concatenate cell contents together into another cell, but strings can be put directly in the function definition, too. This means text can be inserted into the result directly via the function call—it doesn’t need to already be in the Sheet somewhere.

What’s it good for?

The primary use case for this function in my life is when I’m on a project that involves either adding new or updating existing data based on some Excel sheet of Business data that gets handed my way. I’ll use this to build UPDATEs or an INSERT statement to get this data into the DB quickly and easily.

I’m going to build a quick example using the Sales.SalesPerson table in AdventureWorks.

For the sake of argument, let’s say that everyone is getting their commission rate adjusted and it’s going to be easier to do it directly in the table instead of through the UI (this would be more plausible if the company were larger and had more than 17 rows in this table, but that’s my story and I’m sticking to it). Or, maybe there isn’t a UI, because “when or why would we ever need to change the commission rates? Don’t waste time on that interface.” But that never happens.

Anyway, the VP of Sales sends you the following Workbook that contains new commission percentages for all of the sales folk:


I’m making this somewhat easy by including the BusinessEntityID column, which is the Natural Key for this data. Such data makes this exercise quite easy, and it’s usually possible to get the business to include this important piece of data as long as you know this activity is coming and can talk to them ahead of time.

When building statements with CONCATENATE, I usually start out in SSMS and manually type out what the first statement would be:

UPDATE sales.SalesPerson
SET CommissionPct = 0.021
WHERE BusinessEntityID = 274

Most of the time, I type out the Excel function call manually based on this initial statement, but it can be copied in as well, after stripping off some of its formatting (line breaks & tabs). The values for the SET & WHERE clauses are replaced by the cells that contain those particular pieces of data. Due to the way the CONCATENATE function works, the “pieces” to concatenate are separated by commas. Wrap plain text to include in the final result in double quotes. The end result should look something like this:

CONCATENATE("UPDATE sales.SalesPerson set CommissionPct = ", C2, " where BusinessEntityID = ",A2)

Put an equals sign at the begging & paste that into Excel. Some magic happens and, bam, UPDATE statement:


Copy that out and paste it in SSMS underneath the one written earlier & compare the two. If they match, the formula is good. The rest is simply a matter of filling the series down. All of the necessary UPDATE statements are done! Don’t skip checking those over to make sure nothing crazy is going on.

The same thing can be done with INSERT statements if a big block of new data is going into a table. Even if you prefer to do a single INSERT statement instead of discrete ones, CONCATENATE is still useful to build SELECTs and UNION/-ALLs to feed into a single INSERT:

CONCATENATE("SELECT '", B3, "', getdate(), ", A3, " UNION ALL")


I think it is. Even if you usually use SSIS to do this type of work, I bet there are still times when using Excel to cook up some UPDATE statements in a hurry is both quicker and easier than writing out a package to do it, or maybe even than the Import wizard.

I use this on a regular basis and believe it’s one of the more useful non-BI or Expense Report things you can do with Excel 😉

Q1 2011 Goals Update: Career Indecision Ahoy!

I tossed around whether or not I wanted to make update posts like this one throughout the year. I can come up with arguments for both sides, and decided to not make a firm choice one way or the other: Basically, if something big has happened in the last quarter as it relates to the goals, then I’ll talk about it. If I’m bombing terribly, it will get saved for one big “look how much I suck” post at the end of the year. In this case, since things are a lot more muddy than I expected right now, I thought I would talk about it a little bit.

First, for a quick recap, here are the high-level things I’m after in 2011:

  • Pick a career direction
  • SQL-related travel
  • Do more experimentation/work with SQL at home
  • Read more
  • Keep the blog going at a decent rate
  • Ride the bicycle
  • Start flying again (this sort of depends on cash situation)

These were covered in my original Goals post, and the first one, career direction, was followed up with on T-SQL Tuesday #14. Lots of these build on each other, with almost everything being dependent on career direction, because of the way my personality works and related improvements that will (theoretically) bring.

On that note…

On picking a direction and sticking to it: Help, it’s dark and scary

Long story short, I still haven’t made a decision. This fails my goal of getting this done by the end of the first quarter. The BI option is starting to take shape, but it’s not turning out to be quite as cut-and-dried as I had thought it was going to be (of course).

There are a lot of moving parts here, with a lot to consider. I’m trying to work through this logically, but I’m not the best at things like this that don’t have clear answers. I’ve taken the steps of listing out pros and cons of each side, but honestly, neither option really stands out as better than the other one.

Going BI has the leg-up as being “what I’ve wanted to do since I was an Accidental DBA”, but even that is tempered: The only part of the MS BI stack we’ll be using long-term is Analysis Services. Don’t get me wrong, I’ve always been really excited by SSAS and if I had to pick one totally new thing to learn in Data Land, SSAS and MDX would be it (I can count those two as one thing, right?). But that’s all we’re using. Its source Dimensional model will be on Oracle. Most of the source data will come from Oracle, as there’s not very much SQL Server.

I will learn a lot of platform-independent BI stuff, which would be great…Except, at the end of the day, I’m mostly a tool/platform guy, and this isn’t going to be a lot of my desired platform. I’m worried about causing problems for myself over the long-term.

I’m frustrated, which of course doesn’t help at all. I’ll talk more about this when it’s all done and over.

SQL Travel

Wellllll, there hasn’t been any. Should we have gone to SQL Saturday #70? Probably. Would it have been great to hang out with the cool kids at #67 the other weekend? Oh yeah. Is SQL Rally going to happen for us? Questionable.

But there is good news on this front: I think the dog sitter bit is worked out. This opens up the door a lot, and we’ve been eyeballing SQL Sat 77 in Pensacola and 75 in Columbus. I also just noticed that Birmingham’s date has been set & they’re going to be #81. That’s really close, too. Surely one of these three will fit on the schedule & in the budget.

That’s pretty much it. I know, not much progress, and a bit of outright failure. Spring is here, though, and that means a generally better mood; almost guarantees more progress.

Meme Monday #1: Spooky Asparagus

See Thomas’s post here for what on earth is going on.

After work, distracted by asparagus, forgot scheduled duties—coworkers left hanging 🙁

Spooky Asparagus

When you’re not paying attention and don’t see this until you’re about 3 feet away from it, it scares the crap out of you.

I’m pretty sure I only have one person to tag for this, so Tammy, get crackin’!

Late edit: Credit for the “Spooky Asparagus” phase goes to @SQLCheesecake, because he’s awesome like that.