If you are reading this, you may be searching up ways to be efficient when working with a spreadsheet or programming. This could be because you spent A LOT of time performing programming tasks that you want to speed up. This post is for you, so keep reading to pick up a trick that could help you out in the office!

“Ain’t nobody got time for that!”

– Kimberly “Sweet Brown” Wilkins

Before we begin, I should mention that I have used this technique in Microsoft Excel, but other tools, such as Google Sheets could be used as well. It is a technique that allows you as a programmer to quicken aspects of your tasks, such as something that is going to repeat itself, or will need some analysis before plugging it in somewhere else. Let’s dive a little deeper.

Microsoft Excel

Google Sheets

Something To Think About

Before we get to the explanation, let’s start with an example. Imagine you have a large list of newly hired employees (Adventure Works – am I right?). You need to create each employee in your key fob system. Additionally, every employee must be assigned to specific access groups using a SQL command or a stored procedure that grants the appropriate access. You have 300 employees in this list and need something that can automate the process. Nobody wants to sit at a desk for 4 hours entering employee data, which is going to put you behind on many other things.

READ MORE: Using Chat to Conquer Your Code

Dynamic SQL Query Creation

This is where the formula bar can save you a ton of time writing SQL queries. It is usually located in the top section of the spreadsheet software. See below:

Looking at this spreadsheet dataset, you can see that we are given the employee’s ID number, their first name, and their last name. This is for simplicity, but it just showcases how we can plug in certain things fairly easily using the formula bar. Let’s keep going!

The Formula

Look at that! Writing out a combination of formula and SQL query produces a dynamic query that can be applied to each user, and appears in seconds. Notice I have used quotations and cell references to build out a stored procedure call for each employee.

The formula is written as such, and can be pasted exactly as written into the formula bar if your data is stored in the same cell numbers.

="EXEC CreateNewEmployee @ID = " & A2 & ", @First = '" & B2 & "', @Last = '" & C2 & "';"

="EXEC CreateNewEmployee @ID = " & A2 & ", @First = '" & B2 & "', @Last = '" & C2 & "';"

Lastly, use the small handle in the bottom-right of the cell you just pasted the formula into, and drag that formula into the other cells below it. That will cause the formula to dynamically adapt cell numbers based on row number, so if the first 3 cell numbers are A2, B2, and C2 on the 2nd row, the formula will use A3, B3, and C3 on the 3rd row, and so on. Copy the “SQL” column and paste it into your SQL editor to run. I prefer using SQL Server Management Studio which performs very well, and have been able to paste up to 200 entries or more!

Wrapping Up

In conclusion, you might be having a long day and your list of tasks is just too long, and getting longer. You don’t have the time to sit and paste information all day, and need a little something to cut your workload down a little. Utilize your preferred spreadsheet software to perform the technique discussed in this post, which will dynamically build code that needs to be used many times over due to distinct data that differs for each iteration.

Leave a comment on this post to discuss any other tricks or tips you have found along the way!