Obscure Problems and Gotchas

It’s amazing to me the reactions I get when I show people seemingly simple tricks on how to automate or generate code and/or script generation. Whenever I see a pattern, that means there is a way to simplify or automate it. Here are some very simple tips and tricks to help you get some of that mundane and repetitive work out of the way quickly.

The CONCATENATE function is a nice hidden gem that many people apparently don’t know about, this has helped me get many things done quickly and because Excel is tabular, it is very easy to work with and hone in on specifics using formulas. The best way to understand how to use this is by example of course.

If you were not aware, everything in Excel uses VB, you can even write your own VB Modules (Static Classes) and use them in your sheets, therefore some of the syntax may be a little strange or familiar to you depending on how much legacy coding you have done. This example is showing three very simple ways to use the concatenate function.

The formulas used for Columns C through E are as follows:

' Column C: Concatenate the names into "First, Last" format
=CONCATENATE($A2, ", ", $B2)

' Column D: Concatenate the names into "Last, First" format
=CONCATENATE($B2, ", ", $A2)

' Column E: Concatenate the names into "First Last" format, but only use the first letter of each to produce initials
=CONCATENATE(LEFT($A2, 1), LEFT($B2, 1))

This is a very simple example of how to use the concatenate function, as you can see it simply allows you to append (aka concatenate) cell contents. If you have never seen this before, let your imagination run wild with what you can do with this. Imagine being able to export data from SQL Server into an excel sheet and playing with the data like this. Go nuts.

SQL Scripting Examples

Now that you know how to concatenate cells together, think about how much easier it is to manipulate data in a database when you are trying to clean up your data or insert data for a first time.

Let’s assume that there is a Person table that you are creating, it has 4 columns in it (ID, First, Last, Age) and you got your data from someone in an Excel sheet or managed to just put it in an Excel sheet yourself. You can generate the insert statements when you are happy with your column names and data.

' Column F: Creating an Insert Statement for sheet data
=CONCATENATE("INSERT INTO Person (First, Last, Age) VALUES ('",B2, "', '", C2, "', ", D2, ");")

Let’s say you made a mistake with some of the data upon insert and now you need to update that data. In the image below you will see that Column D is the old data and Column E is the corrected data. I kept column D in the shot so you can see the differences.

' Column G: Creating an Update Statement for sheet data
=CONCATENATE("UPDATE Person SET Age = ", E2, " WHERE ID = ", A2,";")

This is a rather annoying thing to find out the hard way, if you need to include a soft quote [“] in your formula as text, then you must escape it. 

' Row 2
=CONCATENATE("I am totally """, A2, """ to your complaints.")

' Row 3
=CONCATENATE("I do not """, A3, """ about your cat photos.")

' Row 4
=CONCATENATE("I need these Jennifer Lawrence photos for """, A4, """.")

This methodology is very useful when you have all of the data you want to manipulate right in SQL Server and instead of using Excel, you can just work directly in SSMS using tSQL to get your work done. The difference here is instead of using a function or method – we will be using a tSQL operator which is the plus sign “+”. 

This operator of course will do different things depending on the context. Therefore tSQL is not forgiving (by design) in this respect, where as in Excel it didn’t matter what your data was. In Excel your data will be converted to a string automatically and appended together into text. Therefore keep in mind that you must convert your non-string values into strings in order to concatenate them together. Ok well let’s dive right into this shall we?

For the sake of this example let’s assume the Person table exists in a Dummy database. This Person table is an exact match from the previous section. In fact, I used those insert statements to insert the data. Here is the SQL for the Person table:

CREATE TABLE [dbo].[Person](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [First] [varchar](50) NOT NULL,
 [Last] [varchar](50) NOT NULL,
 [Age] [varchar](50) NOT NULL
) ON [PRIMARY]

Since the table has data in it, I will go ahead and re-perform the simple example from the previous section, but using tSQL this time:

Execution results of the query below

This virtual table was achieved by executing the following SQL:

SELECT
 FirstLast = First + ', ' + Last,
 LastFirst = Last + ', ' + First,
 Initials = LEFT(First, 1) + LEFT(Last, 1)
FROM Person

Notice that this example is basically exactly the same as the simple example in the previous section.

SQL Scripting Example

Since the data is already inserted I am going to skip that as an example and go right into the update scenario. Let’s assume that you want to age everyone by one year, then you can do the following:

Execution results of the query below

This virtual table was achieved by executing the following SQL:

SELECT
 sqlUpdate = 'UPDATE Person SET Age = ' + cast((Age + 1) as varchar(10)) + ' WHERE ID = ' + cast(ID as varchar(10))
FROM Person

Notice how the integers have to be casted as varchar in order to be concatenated properly. Now this isn’t exactly the best example because realistically you can just write that update statement directly in SSMS and execute it in one shot, but this is just a demonstration to open up the topic. If you have not seen this before, then this should open up several doors for you. Some people just don’t think to do this is all.

Generating an Enumeration from a Lookup Table Example

This is a stronger example of how to use tSQL to generate something more useful for you. Let’s assume that you have a PersonType lookup table in your Dummy database as follows:

CREATE TABLE [dbo].[PersonType](
 [PersonTypeId] [int] NOT NULL,
 [Description] [varchar](50) NOT NULL
) ON [PRIMARY]

This table describes different types of people, but you want to code against it as an enumeration. In that case then why bother writing it by hand if you have a lookup table already. The following screenshot shows the data and enumerations written out already:

Execution results of the query below

This is accomplished by running the following SQL:

SELECT
 PersonTypeId,
 Description,
 Enum = Description + ' = ' + cast(PersonTypeId as varchar(10)) + ', ' 
FROM PersonType

The only thing you have to do now is copy and paste the output into Visual Studio and surround it with enumeration syntax. Don’t forget to get rid of that last comma.

Bonus Tip (If you didn’t know already that is)

You can display a lot of information about a table by just highlighting its name and pressing “Alt + F1”. This is by far one of the most useful things you will ever use in SSMS. This is unique to SSMS in that it is a keyboard combination trick. You can quickly remind yourself of a table’s structure or column size without having to use the object explorer and it keeps your hands on the keyboard which is good for productivity.

Example using the Person table:

tSQL Knowledge Milestone

Conclusion

Work smart not hard. These are all tricks I have picked up over the years from other people and things I have discovered on my own. Many of these things are what have compelled me to work on the Simple Class Creator that I produced, because once again, “If there is a pattern – then you can automate it!”