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:
- Script SQL Server objects using DBATools
- All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool
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:
Select the specific table for which we need the script. We can select multiple objects as well for scripting:
In the next page, you’ll find scripting options like:
- Save to file
- Save to Clipboard
- Save to a new query window
Click on Advanced (4) and view 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:
It generates the Insert statement for all data rows. In the following image, we see a glimpse of insert statements:
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:
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:
Click on Install, and it asks permissions for opening an external website:
Click on Open and in the GitHub page, and you see a file simple-data-scripter-0.1.0.vsix as shown below:
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:
Select previously downloaded VSIX file and install it:
You get a prompt for accepting the security risk because it is a third party extension:
Click Yes, and it quickly installs this data scripter extension:
You have to reload Azure Data Studio to activate the extension:
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:
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:
|
1 2 |
SELECT * FROM [SQLShack].[dbo].[Employee]; |
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:
We get the script in the following screenshot. This script is in two parts.
- Create table statement: It scripts all output columns with appropriate data types in a temporary table. By default, it comments the create table statement
- Insert statement: it generates an insert statement with all values from the source table
We can modify the temporary table with an appropriate table name, uncomment the create table statement:
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:
Once connected, select the destination database from the drop-down list:
Click on Run to execute this script. In the output, we see that six rows inserted successfully:
Verify the records from the table using the SELECT statement:
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:
Click on the query and modify it to select records with employee id greater than 4 as shown below:
Press Enter and review the generated script. In the script, notice that it includes records for Employee id five and six:
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:
Specify this query into the data scripter window:
Press Enter and you get a script with only specified columns:
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:
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:
Press Enter and see our trick works! We get the view output table script and data insertion script as well:
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023