sqlsunday.com – Page 5

Fun with VALUES

There’s more to the VALUES clause in T-SQL than meets the eye. We’ve all used the most basic INSERT syntax:

INSERT INTO #work (a, b, c)
VALUES (10, 20, 30);

But did you know that you can create multiple rows using that same VALUES clause, separated by commas?

INSERT INTO #work (a, b, c)
VALUES (10, 20, 30),
       (11, 21, 31),
       (12, 22, 32);

Note the commas at the end of each line, denoting that a new row begins here. Because this runs as a single statement, the INSERT runs as an atomic operation, meaning that all rows are inserted, or none at all (like if there’s a syntax issue or a constraint violation).

I use this construct all the time to generate scripts to import data from various external sources, like Excel, or even a result set in Management Studio or Azure Data Studio.

Here’s something you can try:

Select a dataset from SSMS or Excel, copy it to the clipboard, and paste it into a new SSMS window.
Select just one of the tabs, then use the “find and replace” feature (Ctrl+H) in SSMS to replace all tabs with the text ', ' (including the apostrophes).
Now, add the text (' at the beginning of each line and '), at the end of each line. The last line obviously won’t need the trailing comma. If you’re handy with SSMS, you can do at least the leading values with a “box select”: holding down the Alt key as you make a zero-width selection over all the rows, then typing the text.

If all of this sounds like a lot of work for you, you might want to try out a little web hack that I wrote. It allows you to paste a tab-delimited dataset, just like the ones you get from Excel or the result pane in SSMS or ADS, into a window and instantly convert it into a T-SQL INSERT statement with the click of a button.

Copying a dataset from SSMS into table.strd.co, then converting it to a multi-row INSERT statement.

Pro tip: in SQL Server Management Studio, use Ctrl+Shift+C to copy not only the results, but also the column names!

Options

First row has headers: instead of inserting the first row of the raw data, the script uses it to map the INSERTed values to the correct columns in the destination table.
Fix nulls: Particularly when exporting from SSMS, we’ll lose information about which values are actually NULL and which ones are actually the text “NULL”. When this option is unchecked, the values will be treated as the text “NULL”, when checked, all values that consist entirely of the text “NULL” will have the surrounding apostrophes removed, so they become actual NULL values.
Pretty: adds some indenting spaces to the output code. This increases the script size by a few bytes, but increases readability.
Table name: Option table name to put in the INSERT INTO header of the script.

And to make sure you sleep well at night, the entire process on table.strd.co happens in the browser – nothing is ever uploaded to the Internet.

SSMS: Search and replace across multiple objects in a database

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Break out of your comfort zone in 2020.

A few years ago, I was in a meeting at a client’s, and the discussion turned to teamwork. I jokingly said “teamwork is for people who cannot be trusted with complex tasks on their own”, at which point the whole room exploded in laughter (well, everyone laughed except the agile coach).

And even though my joke has since become a pinned tweet, it is in all fairness only true in a limited context. In most of our day-to-day work, we need the help of others, and they rely on ours.

Do something else, become better at what you love

Ok, time for a short “professional development” post to balance out all of the technical stuff.

For a couple of years, I worked as a spin instructor on the side. It was initially something I wanted to try for the challenge, it proved to have a lot of other benefits to my life besides the obvious health impact.

I picked up a huge amount of tricks and patterns along the way. Some of them may appear a little abstract, like working with diverse groups of people, dealing with motivations and meeting expectations, getting comfortable with public speaking and managing nervousness. Others are much more tangible, like microphone skills.

Then again, there are plenty of superpowers that will probably never be very relevant to my (current) line of work, like counting in a beat of a song.

But I digress: Having that extracurricular activity has an immense potential to improve your ability to do other things that you love or get paid for. You don’t have to go and apply to your local gym, and it doesn’t even have to be very expensive – just pick anything that challenges you.

You’ll find that even though the specific superpowers from your hobby may not apply at all to your work, there will be unexpected, derived skills and understandings, that will help you grow as a human and as a professional.

I’m sure there’s a management book on the topic, but this is my short version of it. Now, go and do awesome things!

How to use switching to make metadata changes online

Metadata changes, like modifying a clustered index, or many types of column changes, will create locks in SQL Server that will block users from working with that table until the change is completed. In many cases, those locks will extend to the system objects, so you won’t even be able to expand the “Tables” or “Views” nodes in Management Studio.

I want to show you how you can perform those changes using a copy of the table, then instantly switching the table with the copy. The secret is partition switching, and contrary to popular belief, you won’t need Enterprise Edition, or even partitions, to do it.

Aggregating date and time values

Date and time values are not entirely intuitive to aggregate into averages in T-SQL, although the business case does arguably exist. Suppose, for instance, that you have a production log with a “duration” column (in the “time” datatype), and you want to find the totalt or average duration for a certain group of items.

It’s possible, but I would still call it a workaround.

Anonymize your dev databases!

I’ve been working on a little gadget for a while now, and today I finally got around to completing it and so now I’ve published it for everyone to try out. It’s a web API (wait, wait, don’t go away – it’s for database people!) that creates a randomized list of names, addresses, etc.

In this post, I’ll show you how easy it is to use this service to anonymize a development or test database so you don’t have all that personally identifiable information floating around.

Scan hundreds of SQL Saturday raffle tickets. Fast.

If you’re an organizer, sponsor and/or an exhibitor at a SQL Saturday event, you often collect raffle tickets from attendees. In exchange for giving you their contact information, they have the opportunity to win cool raffle prizes.

Here’s the thing, though. Raffle tickets can be a bit painful to scan on your mobile phone. You point the phone at the QR code, then click the URL that opens up the browser, after which the SQL Saturday web service takes another second or two (or three) to load. This is fine when you have just a few tickets, but it can be mindnumbing if you have hundreds.

What can I say, I’m lazy.

Calculating invoice settlements with window functions

I’ve solved this puzzle a number of times, but I’ve never really been quite happy with the result. It was either too slow, too much code, too hard to understand. So here’s a fresh take at computing the time-to-payment on a large amount of invoices, with multiple, overlapping, partial payments.