Rajendra Gupta
Output of generated script

Generate data scripts using SSMS and Azure Data Studio

January 13, 2020 by

This article explores different ways to generate data scripts using Azure Data Studio and SQL Server Management Studio.

Introduction

Each organization follows the standard practice of having multiple environments for a database. Usually, we use Production, UAT, Staging, Development terminology for these environments. We get different kinds of requirements for data to refresh from one environment to another such as follows:

  • Refresh a complete production database into a lower environment
  • Refresh only specific tables from production to dev, staging environment
  • Generate scripts (Insert statements) for table data
  • Generate script for output of a select statement(specific data)

In the following articles, we explored different ways of scripting objects in SQL Server:

In this article, we will explore generating scripts using SQL Server Management Studio and Azure Data Studio.

Generate Scripts wizard in SQL Server Management Studio

We get the Generate Scripts wizard option in the task menu of a SQL database, as shown below:

Generate Scripts wizard in SQL Server Management Studio

Select the specific table for which we need the script. We can select multiple objects as well for scripting:

Select database objects

In the next page, you’ll find scripting options like:

  1. Save to file
  2. Save to Clipboard
  3. Save to a new query window

Set Scripting options

Click on Advanced (4) and view advanced scripting options:

Advanced scripting options

I require to generate scripts for data only. We do not want object scripts in the generated script. For this requirement, make the following changes in the advanced scripting options:

  • Script defaults: False
  • Script extended properties: False
  • Type of data to script: Data only
  • Script check constraints: False
  • Script foreign keys: False
  • Script primary keys: False
  • Script Unique keys: False

Click OK and generate script in the new query window. Once all tasks completed in the wizard, you can see the success status of each one:

Script wizard status

It generates the Insert statement for all data rows. In the following image, we see a glimpse of insert statements:

Generated script

We can use this insert statement for data insertion in other environments. It solves the requirement if we want a script for all data rows.

We cannot filter the rows in the Generate Script wizard of SSMS. Let’s explore the solution using Azure Data Studio.

Simple Data Scripter in Azure Data Studio (ADS)

In the previous articles, we have explored various solutions in the ADS extensions. An extension is ADS ability for feature enhancements. It also allows you to design your extension and publish it on the market place for other users.

We should always use the latest version of ADS. In this article, I use November release version 1.13.1:

ADS installation

Launch Azure Data Studio and navigate to Extensions in the left-hand menu bar. We get a list of extensions so you can search for specific extension using keywords. For example, here we see a Simple Data Scripter extension:

Simple Data Scripter

Click on Install, and it asks permissions for opening an external website:

Open URL for installing Simple Data Scripter

Click on Open and in the GitHub page, and you see a file simple-data-scripter-0.1.0.vsix as shown below:

Download VSIX file

Download this VSIX file and save it on the appropriate location. In the Azure Data Studio, go to File, and select Install Extension from VSIX Package:

Install Extension from VSIX Package

Select previously downloaded VSIX file and install it:

Select the VSIX file and Install

You get a prompt for accepting the security risk because it is a third party extension:

accept security risks

Click Yes, and it quickly installs this data scripter extension:

Completing installation

You have to reload Azure Data Studio to activate the extension:

reload Azure Data Studio

Click on Reload Now, and it restarts ADS. You should save any work before reloading ADS.

Generate scripts using the Data Script extension

Connect to a SQL instance in Azure Data Studio, and you get an option Script Table Data in the right-click context menu:

Generate scripts using the data Script extension

It opens the following window with a query to select all records from the selected table. In this example, we require a script for [dbo].[Employee] table, so it shows the following script:

Specify query

We can use the following options for this pop-up:

  • Enter:- Press [Enter] to execute the script and get a script
  • Escape:- Press [ESC] for cancelling query execution

Let’s accept the default query and press Enter to view the script. In the tasks, it shows the status of scripting data for a specific object. We can also note that it took 0 seconds for generating the script:

Tasks status

We get the script in the following screenshot. This script is in two parts.

  1. Create table statement: It scripts all output columns with appropriate data types in a temporary table. By default, it comments the create table statement
  2. Insert statement: it generates an insert statement with all values from the source table

Output of generated script

We can modify the temporary table with an appropriate table name, uncomment the create table statement:

modify the temporary table

Now, click on Connect and specify an existing connection. You can also specify a new connection in case you want to execute this script on a separate instance:

Specify connection

Once connected, select the destination database from the drop-down list:

Select a database

Click on Run to execute this script. In the output, we see that six rows inserted successfully:

script output

Verify the records from the table using the SELECT statement:

Verify records

Generate script for selected records using Azure Data Studio

It is an exciting part of the data scripter extension. Suppose we have two copies of a database – Production and Test.

We find out that in the Production database we have records for employee id 1 to 6 while the test database has employee records up to employee id 4. In this case, we want to generate a script for employee id 5 and 6 so that we can insert it into the test database.

Right-click on the table and click Script table data. You get the following pop-up as described earlier as well:

Generate script for selected records

Click on the query and modify it to select records with employee id greater than 4 as shown below:

Specify conditional query

Press Enter and review the generated script. In the script, notice that it includes records for Employee id five and six:

Verify script output

Generate script for few columns into destination table using Azure Data Studio

In the previous example, we generated a script for all columns of a source table and prepared a destination table. Suppose we want a few specific columns in the destination table. We can specify column names using the Data Script extension.

It is better to write the query using specific columns so that we can copy this script in the data script window:

Prepare a query for few columns

Specify this query into the data scripter window:

Paste query with limited columns

Press Enter and you get a script with only specified columns:

Verify output

Generate script from the output of a view that joins multiple tables using Azure Data Studio.

We do not have options to generate a script for the query that joins multiple tables. If we right-click on a view, we do not get the option to generate the script for it:

view options

We can still generate insert statement scripts with a small twist. Right-click on any table in the database and click Script table data.

In the query window, write-down script to select records from a view. This view in the sample database AdventureWorks joins multiple tables together. You can right-click on this view and script as create to view the schema of it:

Generate script from the output of a view that joins multiple tables

Press Enter and see our trick works! We get the view output table script and data insertion script as well:

View the script and Insert statement

Conclusion

In this article, we explored the data scripter extension in Azure Data Studio. We can generate scripts for objects and data insertion as well as using this. I found it a useful extension, and you should explore it using the examples specified above.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

155 Views