Exporting and Importing JSON Data
The article reveals valuable information on how to perform data export and import in a SQL Server database.
In the previous articles, we studied the basic rules of SQL database design, created a database schema diagram for a recruitment service, and filled the newly-created database with test data on employees.
The database contains the following entities:
- Employee
- Company
- Position
- Project
- Skill
This time, let’s consider the ways to transfer data from one SQL Server database to another one through export and import. This can come in handy when the management system of the client database is an older version than the data source. Meaning, when it is impossible to use a backup copy or when only a part of data has to be transferred, as all data weighs too much.
Export data from SQL Server to JSON tables
Firstly, the functionality for data export is available in dbForge Studio for SQL Server as well as dbForge Data Pump. When it comes to filling SQL databases with external source data and migrating data between systems, Data Pump is the add-in that is always of great help.
Let us first consider its use in SSMS.
To begin with, let us perform data export.
To do this, in SSMS, right-click on the database you need, and in the drop-down menu, select the Data Pump\ Export Data… command:
The export settings window will then appear. On the “Export format” tab, you can select different formats. We select JSON format in our case, to transfer data and import it into another database. Let’s then press the “Next” button:
Next, on the “Source” tab, you need to select the necessary tables for export. In this case, we select all user tables and press the “Next” button:
Following that, on the “Data formats” tab, you need to select the required columns on the “Columns” tab and setup data formats on the “Formats” tab. In the present case, we choose all columns and leave settings of the “Formats” at their defaults. Then, press the “Next” button:
On the “Output settings” tab, we need to configure the output parameters of the exported data. Here, we leave the default settings and click on the “Next” button:
On the “Exported rows” tab, you can configure the number of exported rows, depending on whether you need to export all rows or only a certain part of them. In this case, we leave the settings as default, that is, we will export rows. Now press the “Next” button:
Next, on the “Errors handling” tab, you can specify the settings for the output of runtime errors during export. Leave the settings as default. Note that for later use, you can save all the specified settings as a template by clicking the “Save Template…” button in the left bottom corner. Now, you should press the “Export” button to start data export:
While export is running, you can view the process of data extraction on the “Export” tab.
Upon the completion, you will see the “Finish” tab, where you can either close the window by pressing the “Finish” button or immediately go to the folder that contains generated files by pressing the “Open result folder…” button:
Finally, you can see the folder with generated SQL database data in JSON files:
Let’s open the contents of the dbo_Employee file:
To sum up, we exported the data of the new database to files with JSON format.
Note that export can be useful when exchanging data between two types of database management systems.
It is also quite handy to export data in Excel, to provide some data for analysts to research.
Import JSON data to a SQL Server database
Now, let us copy the resulting data to the necessary server and restore the JobEmpl2 database there.
To do this, we need to create a new database, JobEmpl2, with the same schema as the JobEmpl using the following script:
This script can be obtained in a number of ways. For instance, using the Compare Bundle for SQL Server tool.
Right-click on it and in the drop-down menu, select the Data Pump\ “Import Data…” command:
Similarly to export, on the “Source file” tab, select the JSON format and the file itself. Then press the “Next” button:
On the “Destination” tab, pick the existing Employee table in the dbo schema of JobEmpl2 database and then press “Next”:
Next, on the “Options” tab, you can set encoding and see what the inserted data will look like. Leave the settings at their defaults and press the “Next” key:
On the “Data formats” tab, you can configure the format of inserted data. Here, we leave the default values and press the “Next” button:
On the “Mapping” tab, you can configure the mapping of the source and target fields. Let’s leave everything as it is and press the “Next” button:
Following that, on the “Modes” tab, you can set the data import mode. In our case, we leave adding data without deleting the existing data by means of the bulk insert. Then, press “Next”:
On the “Output” tab, you can configure the target place for the data to be imported. In this case, select import data directly to the database and click “Next”:
Thereafter, on the “Errors handling” tab, you can configure the output of runtime errors during the import process. You can also save the previous settings as a template by clicking on the “Save Template…” button situated in the left bottom corner. We leave the default settings and press the “Import” button to start import:
During the import process, on the “Import” tab, you can track the execution progress. As soon as the import is complete, the result is displayed on the “Finish” tab. Press the “Finish” button to close the window:
When you query the destination database, you can make sure that the data was successfully imported to the Employee table:
By the same token, you can import the remaining files to the corresponding tables.
You can perform data export and import with the help of standard functionality of SSMS by right-clicking on the necessary database and selecting the corresponding command in the drop-down menu:
Conclusion
To put it briefly, data export and import allow not only to move data between databases and to provide data in a convenient form for subsequent processing but also to import the part of the necessary data into the required database.
In the next part, we will examine how to conduct the object and data search in the created database.
Originally published at https://blog.devart.com on July 23, 2020.
