How to export and import JSON data via dbForge Data Pump for SQL Server

July 23rd, 2020

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 structure diagram for a recruitment service

Img.1. The database structure diagram for a recruitment service

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:

Export data from the table using Data Pump

Img.2. SQL Data export using Data Pump

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:

Select JSON format for export

Img.3. Setting the “Export format” tab

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:

Select the tables for export

Img.4. Setting the “Source” tab

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:

Select the required columns for export

Img.5. Setting the “Data formats” tab

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:

Configure the output parameters of the data to export

Img.6. The “Output settings” tab

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:

Select the number of rows for export

Img.7. Setting the “Exported rows” tab

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:

Start the export process

Img.8. Setting the “Errors handling” tab

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:

The data export is complete

Img.9. The result of data export

Finally, you can see the folder with generated SQL database data in JSON files:

Generated SQL database files

Img.10. The folder with resulting data files

Let’s open the contents of the dbo_Employee file:

The dbo_Employee file

Img.11. 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:

SET CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS, ARITHABORT, XACT_ABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
GO

USE [JobEmpl2]
GO

IF DB_NAME() <> N'JobEmpl2' SET NOEXEC ON
GO

--
-- Start Transaction
--
BEGIN TRANSACTION
GO

--
-- Create table [dbo].[Skill]
--
CREATE TABLE [dbo].[Skill] (
  [SkillID] [int] IDENTITY,
  [SkillName] [nvarchar](255) NOT NULL,
  CONSTRAINT [PK_Skill_SkillID] PRIMARY KEY CLUSTERED ([SkillID])
)
ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[Project]
--
CREATE TABLE [dbo].[Project] (
  [ProjectID] [int] IDENTITY,
  [ProjectName] [nvarchar](255) NOT NULL,
  [Description] [nvarchar](max) NOT NULL,
  CONSTRAINT [PK_Project_ProjectID] PRIMARY KEY CLUSTERED ([ProjectID])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[ProjectSkill]
--
CREATE TABLE [dbo].[ProjectSkill] (
  [ProjectID] [int] NOT NULL,
  [SkillID] [int] NOT NULL,
  CONSTRAINT [PK_ProjectSkill] PRIMARY KEY CLUSTERED ([ProjectID], [SkillID])
)
ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_ProjectSkill_ProjectID] on table [dbo].[ProjectSkill]
--
ALTER TABLE [dbo].[ProjectSkill] WITH NOCHECK
  ADD CONSTRAINT [FK_ProjectSkill_ProjectID] FOREIGN KEY ([ProjectID]) REFERENCES [dbo].[Project] ([ProjectID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_ProjectSkill_SkillID] on table [dbo].[ProjectSkill]
--
ALTER TABLE [dbo].[ProjectSkill] WITH NOCHECK
  ADD CONSTRAINT [FK_ProjectSkill_SkillID] FOREIGN KEY ([SkillID]) REFERENCES [dbo].[Skill] ([SkillID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[Position]
--
CREATE TABLE [dbo].[Position] (
  [PositionID] [int] IDENTITY,
  [PostitionName] [nvarchar](255) NOT NULL,
  CONSTRAINT [PK_Position_PositionID] PRIMARY KEY CLUSTERED ([PositionID])
)
ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[Employee]
--
CREATE TABLE [dbo].[Employee] (
  [EmployeeID] [int] IDENTITY,
  [FirstName] [nvarchar](255) NOT NULL,
  [LastName] [nvarchar](255) NOT NULL,
  CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ([EmployeeID])
)
ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[Company]
--
CREATE TABLE [dbo].[Company] (
  [CompanyID] [int] IDENTITY,
  [CompanyName] [nvarchar](255) NOT NULL,
  [Description] [nvarchar](255) NOT NULL,
  CONSTRAINT [PK_Company_CompanyID] PRIMARY KEY CLUSTERED ([CompanyID])
)
ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create table [dbo].[JobHistory]
--
CREATE TABLE [dbo].[JobHistory] (
  [EmployeeID] [int] NOT NULL,
  [CompanyID] [int] NOT NULL,
  [PositionID] [int] NOT NULL,
  [ProjectID] [int] NOT NULL,
  [StartDate] [date] NOT NULL,
  [FinishDate] [date] NULL,
  [Description] [nvarchar](max) NOT NULL,
  [Achievements] [nvarchar](max) NULL,
  [ReasonsForLeavingTheProject] [nvarchar](max) NULL,
  [ReasonsForLeavingTheCompany] [nvarchar](max) NULL,
  CONSTRAINT [PK_JobHistory] PRIMARY KEY CLUSTERED ([EmployeeID], [CompanyID], [PositionID], [ProjectID])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_JobHistory_Company_CompanyID] on table [dbo].[JobHistory]
--
ALTER TABLE [dbo].[JobHistory] WITH NOCHECK
  ADD CONSTRAINT [FK_JobHistory_Company_CompanyID] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Company] ([CompanyID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_JobHistory_Employee_EmployeeID] on table [dbo].[JobHistory]
--
ALTER TABLE [dbo].[JobHistory] WITH NOCHECK
  ADD CONSTRAINT [FK_JobHistory_Employee_EmployeeID] FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_JobHistory_Position_PositionID] on table [dbo].[JobHistory]
--
ALTER TABLE [dbo].[JobHistory] WITH NOCHECK
  ADD CONSTRAINT [FK_JobHistory_Position_PositionID] FOREIGN KEY ([PositionID]) REFERENCES [dbo].[Position] ([PositionID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create foreign key [FK_JobHistory_Project_ProjectID] on table [dbo].[JobHistory]
--
ALTER TABLE [dbo].[JobHistory] WITH NOCHECK
  ADD CONSTRAINT [FK_JobHistory_Project_ProjectID] FOREIGN KEY ([ProjectID]) REFERENCES [dbo].[Project] ([ProjectID])
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Commit Transaction
--
IF @@TRANCOUNT>0 COMMIT TRANSACTION
GO

--
-- Set NOEXEC to off
--
SET NOEXEC OFF
GO

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:

Select the data import command

Img.12. Data import command in Data Pump

Similarly to export, on the “Source file” tab, select the JSON format and the file itself. Then press the “Next” button:

Select the JSON format for data import

Img.13. Setting the “Source file” tab

On the “Destination” tab, pick the existing Employee table in the dbo schema of JobEmpl2 database and then press “Next”:

Select the table for data import

Img.14. Setting the “Destination” tab

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:

Set the data import options

Img.15.Setting the “Options” tab

On the “Data formats” tab, you can configure the format of inserted data. Here, we leave the default values and press the “Next” button:

Set the format of the inserted data

Img.16. Setting the “Data formats” tab

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:

Configure the source and target fields

Img.17. Setting the “Mapping” tab

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”:

Set the data import mode

Img.18. Setting the “Modes” tab

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”:

The target place for data import

Img.19. Setting the “Output” tab

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:

Initiate data import

Img.20. Setting the “Errors handling” tab

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:

Finish the import process

Img.21. The data import result

When you query the destination database, you can make sure that the data was successfully imported to the Employee table:

The data imported to the Employee table

Img.22. The imported data 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:

Import and export data command in SSMS

Img.23. Data export and import with the standard SSMS tools

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.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment