Voice of the DBA

Author: way0utwest

Bulk Inserting Build Data–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the common tasks that many DBAs need to do is insert data into a database. Often this comes from various sources, but a CSV (comma separate value) format is common. One could use the data import wizard, but that seems to be very flaky with CSVs, so I’ll show a quick way to use the BULK INSERT command.

This command is a way to read files and load them into a table, similar to how bcp works. However, this is a T-SQL command, and can be included inside your database.

The basic format is

BULK INSERT <table>

FROM <source>

WITH <options>

For most CSV imports, this means we need to pick a table, in my case, the BuildStaging table, and a source file. My statement looks like this:

BULK INSERT dbo.BuildStaging
FROM ‘e:\Documents\ssc\BuildList_SQLServer2014.csv’

I also need some options. The basics for a CSV are:

WITH
( FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
);

There could be other items you want to enable, and there is quite a list. In my case, my file looks like this:

I have a header row, so let’s get rid of that by adding a FIRSTROW = 2 option.

Now when I run my command, the data is inserted.

From here, I need to work with the data and clean it futher for insert into other tables.

SQLNewBlogger

This was a quick task I needed to accomplish. I knew most of the syntax, but had to double check the option names, and ended up taking about 2 minutes to import the data and 10-15 to write this post.

And, I’ll likely remember how to do this import after spending time writing about it.

August 31, 2017
Getting the Hyperlink from Excel
I had a spreadsheet of data that contained hyperlinks. In this case, it was a series of Microsoft Knowledge Base Articles with a hyperlink associated with them. I would assume there’s an easy function in Excel to extract these, but apparently there isn’t. That’s certainly a useful function for a data person.

I turned to the handy, dandy Google and found this Q&A and Superuser. I needed to delve back into VBA and build a macro, which is easy, but seems silly. In any case, I pasted this in and then set a formula based on a cell.

And got a 0 in the field. I started to try and debug this, before trying another cell. That one worked. Apparently some of my cells, formatted as blue, underlined text, don’t really have formulas.

No big deal, but good to know.

Here’s the macro formula repeated from the post, just in case.
```
Function GetURL(cell As range, Optional default_value As Variant)
 'Lists the Hyperlink Address for a Given Cell
 'If cell does not contain a hyperlink, return default_value
      If (cell.range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.range("A1").Hyperlinks(1).Address
      End If
End Function
```
August 30, 2017
Database Development Made Easy

I ran across this post on developing database with SSDT. It has a lot of steps, and reading through it, I find this to make some sense, but I’m not sure I think this is easy. I can see why developers find databases to be a pain to work with. There are a lot of steps in this post to just setup and configure a database project. Databases are fundamentally hard to work with, as the model of maintaining state between changes and ensuring data is not lost can be hard. While the concept is similar to keeping track of configuration files, the scale of data in a database is vast and ever changing. Tooling to manage data that might need to be recovered isn’t very practical.

I ran across a developer that was trying to automate their database development. They used tooling to deploy a table to a production system. The application connected and data was stored in the table. The developer then dropped a column from a table and deployed this change. It worked, but this was a mistake and this person decided to deploy the previous version of the database, with the additional column restored. The deployment worked, but there wasn’t data in the column that had been dropped, and added back. Why not, asked the developer?

Many people are of two minds here. One, any tooling or automation should preserve data and allow for rollbacks. In the application world, this makes perfect sense, and even the data our application uses (reference files, configuration files, etc.) are restored if we rollback to a previous version.

For the data people, this makes no sense. The data in a column could be of significant size. We often plan for systems to reach millions (or more) rows of data, and trying to save the state of this data before a change isn’t practical. Even if we were to store changes items for a few deployments, it’s entirely possible that putting the data back wouldn’t make sense as related information in other tables might not match up correctly. Consider the case of a financial system and restoring old money values. Who knows what issues would be created?

For developers, this highlights one of the things they dislike about databases. They are must manage state transitions across deployments, and rolling back to previous versions isn’t often possible. This is one reason that I have often performed a backup before major deployments, and even today, in an automated DevOps process, I’d want to perform a backup if any significant data were being changed or deleted.

I’ve spent a lot of time advocating for DevOps and smoother, modern database development practices for the last few years. I don’t want the database to be a hindrance or impediment to change, but I also don’t want to compromise the integrity or safety of data. My pitch has always been that our database automation tools at Redgate don’t perform any magic. They smooth, and hopefully speed up, the process of making database changes that we’ve used for decades. They save you time and effort, just as other tools may do, but they can’t change the rules of relational database changes.

Everyone developing code inside or connecting to a database needs to understand how transactions and data changes work. There are rules and restrictions the protect our data. These mean we need to sometimes plan and consider the consequences of our actions. This should also mean that despite wanting to move faster and make changes, we can’t treat data placed in columns as malleable in the way a method in C# can be changed back and forth. We need to account for, and protect, the information stored in our systems. There are patterns that can help you evolve your database from one state to the next, but there isn’t any magic that lets you drop and add data storage elements without some preparation for handling the data itself.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 30, 2017
The Game Night Poll

This year we’ve handed off Game Night to the PASS organization and it’s an official event. Volunteers from the community will help setup, manage, and tear down the event. Kevin Hill is taking the lead, but if you’re willing to help run the night, let he or I know.

Kevin has a poll up, asking what games he should invest in for PASS. If there’s something you’d like to play, think others will enjoy, and don’t want to bring your copy, let him know.

Also, don’t forget to purchase a ticket if you are coming to the Summit and want to attend this party on Thursday night. There isn’t a party this night, so Game Night is a low-key, fun, competitive space to enjoy with fellow gaming attendees.

Purchase a Ticket

August 29, 2017