Wednesday, November 19, 2014

How to use Excel and tSQL to help you Generate Code


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.

Using Excel's Concatenate Function

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.

Simple Example

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

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.

Inserting New Data

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 E is hidden on purpose for this example
The formula used for Column F is:

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

As you can see that formula is a little long and hard to read, but the pattern is simple - you are stringing the columns together and formatting them according to data type; just like you would if you were writing it by hand in SSMS. Please note that the ID column (Column A) is assumed to be sequential integers and that the Person table has an auto-incrementing identity column named "ID".

Updating Existing Data

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 F is hidden on purpose for this example
The formula used for Column G is:

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

As you can see, you can easily fix your data now by mass producing these update statements.

General Precautions

Be very careful when producing these kinds of statements and make sure to triple check your statements before executing them. Work with a single statement to make sure it works first before applying the formula to an entire column or group of cells. There have been times where I don't realize I have either referenced the wrong cell or downshifted a cell by accident which leads to trouble.

Keep in mind these are single hits, this is not intended for large volumes as your DBA would probably kill you if he found out that you were executing several hundred statements in this format. This is more for smaller sets of data, where writing complex SQL Statements just isn't worth the hassle. This methodology allows you to bypass syntax restrictions and annoying table joins most of all.

Excel Knowledge Milestone

If you have not seen this before, then I hope your mind is blown and you can go nuts with it. I showed SQL generation examples, but this can be used for literally anything. I have generated classes and HTML markup using this methodology. Make sure to explore the other functions in Excel that will help you along with your coding.

On an Aside: Soft Quotes in Formulas

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. 

In order to escape soft quotes you need to double up on the soft quotes. In other words, for every soft quote you must add a soft quote if you want it to be a literal. Here are the formulas for the above:

' 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, """.")

Using tSQL's Concatenation Operator

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?

Simple Example

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

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:

 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:

 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

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:

 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

Once again, if you did not know about any of this, I hope that this helps you get your mundane portions of your work done more quickly. Honestly there is no point in writing out really boring repetitive stuff when you don't have to. Explore more ways to work with tSQL Concatenation - there are a wonderful number of functions provided by Microsoft for dealing with string manipulation in tSQL.


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!"

No comments:

Post a Comment