Increasing database development productivity with SQL Complete (Part 2)

Moving forward with the series, in this article, I will continue with the coverage of the SQL IntelliSense tool to boost DB Devs’ productivity. If you missed Part 1 and want to catch up before diving into this one, follow this link and come back once you’re done.

Ways to export the data
While working with different result sets, how often have you needed to move/export the data to someplace else, regardless of the reason? I have had that need way too many times in the past, and SQL Server Management Studio lets you export the data as a .csv file right out of the box. However, when you super-charge SSMS with the SQL IntelliSense tool, many context menus will now have myriad additional functionalities, so let’s look at what the tool can do for us in data exporting.

When you right-click in the result set grid, you will see the following additional options:

• Below XML, there are also HTML and JSON options (yes, HTML). It’s just that those didn’t cut while I was taking the screenshot.

Generate Script As
This one is very valuable to me because it can serve many purposes (depending on your particular needs, of course). As the name implies, it will generate a script based on the option that you pick, so let’s explore the very first one, which is “INSERT”:

For simplicity, I will choose the “To New Query Editor Window” option, and this is what I get:

I guess there’s no need to explain the script, but I’d like to mention why I see this as a big feature regardless. To make it easier for the reader’s eye, let me break it down into different points:

1. Backup of an individual table: sometimes, when you are about to apply a data modification query, you’d like to make sure that there’s a backup of the data somewhere so that you can revert to its original state if necessary (assuming you’re past the respective commit of course). But guess what? The native backup solution from Microsoft doesn’t let you either backup or restore individual tables (and 3rd party solutions are not always an option due to highly-constrained budgets), and the other native alternatives imply way too many steps, keeping you out of the productive side of the court, unfortunately.
   However, with this solution, you can script the entire table with a couple of clicks and have it sitting there in case you need it. Or you can immediately restore it someplace else and have a backup of your result set for immediate access.

2. Backup of an individual table with its table definition: there’s a tiny catch with the previous point. You don’t have a table definition to swiftly put the data somewhere else and call it a day. However, I’ve found a neat workaround that can also be very practical: the “INSERT #tmpTable” option.

You just have to remove the “#” symbol, and you’re good to go! Remember that doing this will not carry over any indexes, constraints, triggers, etc., but it is still very useful to move the data around easily.

Copy Data As
Moving to the next category, let’s explore the options that the tool has to offer:

Copy Column Names Only
This one is pretty straightforward. From the result set in the result grid, select all or a few columns and hit “Copy Column Names Only.” After that, you can paste the column names anywhere, and you’re ready. Sometimes, this is quite handy when you want to quickly identify the header of a report, in case you just copied the data without it.

Copy Column Names and Types
Very similar to the previous one, except that it adds the data type of each column to the mix. Perhaps you are thinking of creating an empty temp table or temp variable to work in an intermediate step of your logic, results in a very handy option to get yourself going.

The real beauty of this feature shines with queries where you join two or more tables instead of going through the tables within the Object Explorer to know any column data type that you’re curious about. You can simply right-click the column(s) you want and you’re set!

Moving forward with the rest of the options, even though it is quite obvious that it is an export of the data in CSV, XML, HTML, and JSON formats, let’s see how easy it is to do so.

*All four options offer an export “To File…” and “To Clipboard.”

CSV

XML

HTML

JSON

I think it’s pretty convenient to have these export formats options right at the tip of your finger, so that you can choose the one that fits your particular needs at any given time.

Important Note

I would like to remark that the data that will be exported will be exactly what you select in the result grid, so make sure to select everything if that’s what you want.

After seeing all the options we get from the result grid, let’s look at what options we get if we right-click any table from Object Explorer.

As you can see, we have one option called “Script Table as CRUD”, so let’s see what it does.

Interestingly enough, it will generate for us the TSQL code to create 4 Stored Procedures: one for SELECT, one for INSERT, one for UPDATE, and one for DELETE. You don’t have to use them if you don’t want to, but it is nice to have if you quickly need your application to interface with your database. You can also tweak the generated TSQL to fully meet your custom needs, but the amount of code generated for you should help save a good chunk of time.

Final thoughts

1. Even if the presented options seem like simple/trivial things, they are present in some shape/form in the day-to-day tasks of a database developer, so SQL Autocomplete can help you boost your productivity in any of those areas.

2. As I’ve mentioned before, the tool by itself is not going to boost your productivity right away. It requires a time investment from your end, but make no mistake about it. The return on that investment that you are making is definitely worth it. And, as we saw in this article, SQL Complete smoothens the learning curve by a mile.

3. Stay tuned for more articles where we can see how SQL Complete can help you boost your productivity!