How to Import New Database in Sql Server

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.

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

2. Select 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":

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.
   

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.

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

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.

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.

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:
   

10. View the scripts

    As a result, there will be 3 scripts generated for each directory table:
    

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:

Fig.12. Selecting data import on the database 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.

• Author
• Recent Posts

Evgeniy Gribkov

Evgeniy is an MS SQL Server database analyst, developer, and administrator. He is involved in the development and testing of the SQL Server database management tools. Evgeniy also writes SQL Server-related articles.

data export, data import, data pump, sql file, sql script, sql server, ssis, ssms

How to Import New Database in Sql Server

Source: https://blog.devart.com/how-to-export-and-import-sql-server-database-data-to-a-sql-script.html

Share this post