How to Export and Import SQL Server Database Data to a SQL Script

August 17th, 2020

We quite often face the task of migrating database directory data. And the database for a recruitment service, which we created in the previous series of articles, is no exception. In this article, we are going to demonstrate how you can export data to a .sql file and then import it to the target database. Is it worth mentioning that you can either migrate the whole database or certain objects depending on your purpose.

To start with, you should know that this issue can be solved in a great many ways, including the following:

  1. With dbForge Data Compare for SQL Server.
  2. Using data export tools embedded in SSMS.
  3. Via SSIS package implementation.

However, most of the solutions mentioned above are too cumbersome when it comes to migrating one or several tables. 

Hence, in this article, we are going to have a look at the Data Pump solution, which is also a part of dbForge Studio for SQL Server. This tool is sure to facilitate data import and export as it offers advanced options, templates, and a number of widely used data formats for both export and import.

You can find a step-by-step guide to designing a database for a recruitment service right here.

The database schema for a recruitment service

Fig.1. The database schema for a recruitment service

How to export SQL Server data to a SQL script

  1. Select data export on the database level

    So, let’s get started! Select the required database and right-click the necessary table (if you need to export a specific table) or the database (if you need to export several tables) and select “Export Data”: Data export on the database level

  2. Select data export on the table level

    Selecting data export on the table level

  3. Select the export format

    Next, on the “Export format” tab, you can choose from different formats of data export. We choose SQL scripts and press “Next”:Selecting the export format

  4. Select data to export

    Now, on the “Source” tab, select the tables to export data from and press “Next”.
    In this case, we select three directories:
    1. “Company” is a list of companies.
    2. “Position” is the list of positions.
    3. “Skill” is a list of skills.
    Note that it is possible to change the connection and the database whenever required.
    The source data for export

  5. Select the type of script generation

    Next, on the “Options” tab, select the type of script generation for data export and select if you need to include the database name in the script. Then, click “Next”.
    Note that the window suggests 4 types of script generation for data export:
    1. INSERT. The script for inserting data will be generated. 
    2. UPDATE. The script for updating data will be generated, that is, the matching key fields will be found and the update will be performed.
    3. DELETE. The script for deleting data will be generated, that is, all data that matches the exported data by key fields on the target database side will be deleted.
    4. MERGE. The script for merging data will be generated, it will include the first two types: insert and update. The statement types in the data export options

  6. Select columns and key fields for export

    Now, on the “Table columns” tab, you need to select the required columns and key fields for export (by default we select all columns for export, and the key fields match primary keys’ definitions of the corresponding tables). Then, click “Next”: The table columns tab where you can select the columns and key field for export

  7. Select data to be exported

    Following that, on the “Exported rows” tab, select which data to export and press “Next”.
    Note that you can select all rows as well as an exact range of rows for data export. Exported rows tab where you can select a range of rows or all rows for export

  8. Set errors handling tab

    Additionally, you can configure errors handling parameters on the “Errors handling tab”.
    Note that users often select the “Write a report to a log file” option when they need to analyze the report results. But to make it simple, leave the default options and click “Export” to start exporting data. On the errors handling tab, you can select the way you want to handle errors and configure log settings

  9. Finish the export

    When the export is complete, you can either click “Finish” or open the folder with generated scripts by pressing the “Open result folder..” button:
    Data export completion

  10. View the scripts

    As a result, there will be 3 scripts generated for each directory table:
    The 3 SQL scripts generated for data export

The T-SQL script is going to look the following way:

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

SET IDENTITY_INSERT JobEmplDB.dbo.Skill ON
GO
INSERT JobEmplDB.dbo.Skill(SkillID, SkillName) VALUES (689, N'C#')
...
INSERT JobEmplDB.dbo.Skill(SkillID, SkillName) VALUES (14, N'SQL')
GO
SET IDENTITY_INSERT JobEmplDB.dbo.Skill OFF
GO

You need to apply the generated scripts to the necessary target database.

But what if the data was saved in a different format?

For that purpose, there is data import, which you can open by right-clicking the database or the desired table:

Data import on the database level

Fig.12. Selecting data import on the database level

Data import on the table level

Fig.13. Selecting data import on the table level

Keep going in a similar way as we have performed data export. Also, visit Documentation center to find out more about how to import data from a CSV file. For reference, CSV is a compact text format that is used for storing tabular data. As well as that, it is a very common format as most modern spreadsheet applications (like Excel) can work with files in CSV format (export/import data).

That’s all for now, folks. To summarize, this time we have looked upon data import and export to a SQL file using a highly customizable solution from dbForge Studio for SQL Server.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment