Data migration is a common challenge in the field of database development, management, and administration. This guide will illustrate the process of exporting database data to a .sql file and subsequently importing it into the target database. This method allows for significant versatility: you can either migrate the whole database or certain objects, depending on your purpose. Whether you’re rehosting data on a new server or consolidating databases, these steps will ensure a smooth migration.
This article has been updated on Nov/08/2023. We have added some information on using SSMS to import and export database data, best practices, potential issues that may arise, and solutions to swiftly solve those.
Contents
- Why exporting and importing data is crucial
- Methods to export and import data in SQL Server
- Common pitfalls
- Best practices
- Conclusion
Why exporting and importing data is crucial
To begin with, let us emphasize the significance of being familiar with importing and exporting techniques when working with SQL Server databases. First of all, it enables data backup and recovery in the moments of the most need, safeguarding against potential data loss or corruption. Additionally, it plays a pivotal role in data migration, ensuring a smooth transition when moving to a new server, system, or database platform. Moreover, it facilitates data sharing and collaboration by allowing easy dissemination of information among stakeholders. Lastly, exporting and importing data is integral for database performance optimization, particularly when dealing with large datasets, as it allows for efficient bulk operations.
Methods to export and import data in SQL Server
Moving forward, we will provide you with step-by-step tutorials on different export and import methods. This task can be completed in many ways, including the following:
- Using data export tools embedded in SSMS.
- With dbForge Data Compare for SQL Server.
- Via SSIS package implementation.
In this article, we decided to focus on the most popular and convenient ones: using SSMS and SQL scripts.
Using SQL Server Management Studio (SSMS)
In order to start the SQL Server Import and Export Wizard in SSMS, follow these simple steps:
- In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.
- Expand Databases.
- Right-click a database.
- Point to Tasks.
- Click one of the following options.
- Import Data
- Export Data
The SQL Server Import and Export Wizard can copy data to and from the data sources listed in the table below:
| Data Source | Requirements | Additional Information |
|---|---|---|
| Enterprise databases |
|
SQL Server or SSDT installs the files needed to connect to SQL Server. For other enterprise databases like Oracle or IBM DB2, you need to have the client software installed. Microsoft provides drivers and providers for Oracle, and you can get the Microsoft OLEDB Provider for DB2 v5.0 for Microsoft SQL Server from the Microsoft SQL Server 2016 Feature Pack. |
| Text files (flat files) | No additional files required | |
| Microsoft Excel and Microsoft Access files |
|
Microsoft Office does not install all the files needed to connect to Excel and Access files. You need to download the Microsoft Access Database Engine 2016 Redistributable. |
| Azure data sources |
|
SQL Server Data Tools do not install the files needed to connect to Azure Blob Storage. You need to download the Microsoft SQL Server 2016 Integration Services Feature Pack for Azure. |
| Open source databases | Download additional files for specific databases (e.g., PostgreSQL, MySQL) |
For PostgreSQL, see Connect to a PostgreSQL Data Source. For MySQL, see Connect to a MySQL Data Source. |
| Any other data source with available driver or provider | Download additional files |
For sources with an ODBC driver available, see Connect to an ODBC Data Source. For sources with a .Net Framework Data Provider available, download the required files. For sources with an OLE DB Provider available, download the necessary files. |
| Third-party components for SSIS | May require additional files |
Third-party components providing source and destination capabilities for various data sources are sometimes marketed as add-on products for SQL Server Integration Services (SSIS). |
Export SQL Server data to a SQL script
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.
- Select data export on the database level
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:
- Select data export on the table level
- Select the export format
Next, on the Export format page, you can choose from different formats of data export. We choose SQL scripts and press Next:
- Select data to export
Now, on the Source page, 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.
- Select the script generation method
Next, on the Options page, select the script generation method 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.
- Select columns and key fields for export
Now, on the Table columns page, 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 the primary keys’ definitions of the corresponding tables). Then, click Next.
- Select data to be exported
Following that, on the Exported rows page, 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.
- Set errors handling page
Additionally, you can configure errors handling parameters on the Errors handling page.
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.
- 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:
- 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
GOYou 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:
Selecting data import on the database level:
Selecting data import on the table level:
Keep going in a similar way as we have performed data export.
Common pitfalls
When working with SQL Server data import and export, there are some rather common pitfalls you can encounter sooner or later. Thus, it is better to be informed about those to avoid them in the future. These pitfalls can lead to data inconsistencies, errors, or performance issues. Here are some of the most frequent ones:
- Data type mismatch: One of the most common pitfalls is not matching data types between source and target tables. This can lead to truncation, loss of precision, or conversion errors during the import/export process.
- Missing or incorrect data: Failing to validate data before the import/export operation can result in missing or incorrect information in the target table.
- Improper handling of NULL values: Not properly handling NULL values can lead to unexpected behavior. For instance, if a column is not configured to allow NULLs in the target table, but the source data contains NULLs, it can cause errors.
- Lack of data integrity checks: Neglecting to enforce referential integrity or other constraints can lead to inconsistencies in the target database.
- Insufficient permissions: Users without necessary permissions will not be able to perform data import/export operations at all.
Avoiding these common pitfalls requires careful planning, thorough testing, and a good understanding of both the source and target databases.
Best practices
Taking into account all the common data import and export pitfalls we described in the previous section, the first recommendation suggests itself: avoid those at all costs. However, we will not leave you with just that. There are more tips and tricks to follow for the best experience:
- When importing large amounts of data, having improper indexes on the target table can result in slow performance. Make sure to use appropriate indexes since they can help speed up data retrieval.
- Keep a close eye on duplicates in the source data since they can lead to integrity issues in the target table.
- Always double-check the import/export options before proceeding to avoid accidentally overwriting the existing data in the target table.
- Write efficient SQL queries to retrieve and insert data; avoid unnecessary joins or computations.
- It is important to implement error handling and logging mechanisms since failing to do so can make it challenging to troubleshoot issues during the import/export process.
- Always perform thorough testing in a non-production environment before importing or exporting data on production.
- Disable triggers on the target table(s) before starting the import process and re-enable them once the operation is complete to optimize performance.
Conclusion
That’s all for now, folks. To summarize, this time, we have looked upon data import and export to an SQL file using a highly customizable solution from dbForge Studio for SQL Server.
By the way, to discover how to import and export data in SQL Server, you can watch this video. Additionally, if you want to learn how to import data to an SQL Server database with the help of dbForge Data Pump during the DevOps process, feel free to watch this video. Also, visit the 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).
