Notepad++ Macro and SQL Scripts

I recently saw a comment about building SQL scripts with Excel. While I haven’t gone that route before, the comment did remind me of a quick and dirty way of generating SQL statements with Notepad++ and macros. It can be helpful when you have the right set of data and repetitive steps to take.

Let’s look at an example.

Bring Me a Dream

I use an app on my phone to track my sleep. I keep it simple and only enter the time I went to bed and the time I got out of bed. Over time, I can see how I’m trending and make sure I’m taking care of myself when it comes to getting my 7-8 hours of sleep a night.

If I export all of my sleep records from the app to a CSV file, I get a set of data looking something like this:

That’s only a few days and it’s pretty ugly to read. There’s a lot of extra fluff in addition to the times I went to bed and the total hours of sleep per night.

I’m not concerned with options like snoring and rating my sleep. Let’s say I just want to insert the dates and total hours into a table in SQL Server. There are plenty of ways to do so, but in this case we’re going to build our INSERT scripts in Notepad++ with the use of a macro.

Track Your Macros

I’ll open my CSV in Notepad++, start with my cursor at the beginning of the file, and click the red record button to start recording my macro:

I’ll press Shift+End and Delete to delete the top line mostly consisting of identifiers. The first few rows will look like this with my data to insert on the first row:

Next, I’ll type out my INSERT statement while deleting data I don’t want and using copy/paste to arrange the first row of CSV data to fit. That will leave me with this one INSERT statement on Line 1 and my cursor on line 2:

At this point, I can click the square button to the right of the record button to stop my macro:

With my cursor on line 2, I’ll click the two triangles to run the macro multiple times and I’ll choose to run until the end of the file:

I’ll click hit Run and then see my INSERT statements with the same changes from the first lines applied to the rest of the file:

That can be copied, pasted into SSMS, and executed to insert the new records:

Our records:

If it Fits…

There are plenty of alternatives to macros and building SQL statements. This type of example isn’t something I would use often but there are situations where it fits. Using macros in this way are great for more than building SQL statements so hopefully this can get some wheels turning and help you out with SQL Server and beyond.

Thanks for reading!