Moving data in a table from a production server to a lower environment such as a development server can be done a number of different ways. These include backup\restore, using SSIS to move the data, import\export wizard and scripting the data in SQL Server Management Studio. Azure Data Studio by itself doesn’t have a way to script the data in a table and it does not have the import\export wizard. This is where the Table Data Scripter extension comes in handy.
Like most extensions for Azure Data Studio, this extension is also in a preview state. As of the date of this post, it is currently at version 0.1.1 and is published by Sean Price. The documentation for this extension can be found here, Simple Data Scripter. In the documentation there are a few limitations identified
-
-
- Not all data types are supported, specifically the binary datatypes like text, binary and varbinary
- Has only been tested with SQL Server
- Does not work on a view
-
Some extensions will add options under Preferences. This extension does not add any options. Given that, there is limited ability to configure this, which is OK, not really much of a need to do any configuration.
Just as a review, let’s take a look at how we can script the data in SQL Server Management Studio. After right clicking on the database and going to Tasks…Generate Scripts… When you do, a wizard will pop up and then after a few clicks on next, you will arrive at the screen below. The box highlights the “Types of data to script” option. With this there are three options, Data Only, Schema and Data and finally Schema Only.
When right clicking on a database in Azure Data Studio you will see limited options none of which are related to scripting data. This is also true when right clicking on a table in the database. Here are the options we see by right clicking on a table, as before, none of which are related to scripting data.
In order to script the data in Azure Data Studio we will need to install the Simple Data Scripter extension. This is a pretty straight forward and simple install. You will need to download the .vsix file. You can get to the GitHub to download the file from the documentation for the extension in ADS. You can install it under the File menu. While you will not be prompted to restart ADS when you install it, I usually like to restart ADS anyway. In this case, the extension doesn’t move to the installed extensions until after a restart.
Now that the extension is installed, you will see a new option when you right click on a table. This is another potential limitation of the extension, there isn’t an option to script all the data in the database. Well, at least I haven’t found it yet. But then again, I am not sure having a simple place to script all the data in one place if the best way to go.
When clicked the following screen will appear at the top Azure Data Studio. Initially when we look at it our first thoughts might be, what a bad query. But it does make sense, we are creating a script of all the data in the table.
If you click anywhere, ADS will cancel the query and you will see this pop up in the lower right corner and the above screen will disappear.
If you press enter Azure Data Studio will execute the query and generate the script. You will not see a result set but you will see the script. It will look similar to below. Notice the Create Table statement at the top, it is commented out. The table that is used for the example is named RentalCar. The script is working with a table named #tempRentalCar.
When the script is finished, the Task window will open up at the bottom of the screen. The top two are successful executions while the third failed, reason for the failure will be covered shortly.
You may be wondering if the query can be modified. The answer is yes, however not every type of statement will work. If you want to add a WHERE clause that works just fine. However, as the query become more complex it failed more frequently. I attempted to run a query with a join in it and it failed. That is the failure in the image above. The error stated “Query has no results to return”. However, the query works just fine when running in ADS or SQL Server Management Studio.
Here are some of the statements I have tried.
-
-
- Query with a join – failed
- Query with a sub-query – failed
- Query with a WHERE clause and just one table – succeeded
-
When compared to the expectations, this extension works very well and I can definitely see a use for this.
Thanks for visiting my blog!!