Data import and export tools for SQL Server

SQL Server Import and Export Wizard: What it is and when to use it

If we're talking about data import and export within the Microsoft SQL Server ecosystem, the first thing that comes to mind is SQL Server Import and Export Wizard, a graphical utility for transferring data between different data sources and destinations. It is commonly accessed through tools such as SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT). The wizard guides you through selecting a source and a destination, choosing the data to transfer, mapping columns and data types, and running the transfer.

In practical terms, it is designed to make data migration and basic ETL tasks accessible without requiring you to write a custom script or build a full integration package. You can use it to import data from sources such as Excel files, CSV/text files, and other databases into SQL Server, or export SQL Server data to those destinations. It is particularly convenient for ad-hoc or one-off transfers, while more complex, recurring, or automated operations are usually better handled with SSIS or other ETL solutions.

This is where we'd like to introduce a much more flexible alternative that provides you with import and export wizards as part of a comprehensive IDE that works with SQL Server, Azure SQL Database, and related cloud services. It's called dbForge Studio for SQL Server, and it helps you do the following:

• Export data from SQL Server using 14 data formats
• Import data into SQL Server using 10 data formats
• Migrate data between heterogeneous servers through ODBC
• Use the import and export wizards to tailor the process to your preferences
• Create reusable import and export templates
• Automate regular operations from the command line

SQL Server Import and Export Wizard vs dbForge Studio

Both tools can handle common SQL import/export tasks, but they differ in the level of control and automation they offer. The following table compares their key capabilities.

Now, let's see how easy it is to export and import data in dbForge Studio.

Export data from a SQL Server database using the Data Export wizard

To export data in dbForge Studio, you need to open the Data Export wizard. There are several ways to do it.

• On the Start Page, select Data Pump > Export Data.
• In Database Explorer, right-click the required database or server connection and select Tasks > Export Data.
• In Database Explorer, right-click the required table or view and select Export Data.
• On the menu bar, select Database > Tasks > Export Data.
• In Data Editor, click the Data Export icon or right-click anywhere in the grid and select Export Data. You can export either the selected rows or the entire table.

Choose from the 14 supported export file formats

When the wizard opens, you start with choosing a format. You can export data to the following formats: HTML, TXT, XLS, XLSX, MDB, RTF, PDF, JSON, XML, CSV, ODBC, DBF, SQL, and Google Sheets.

Select data for export

On the Source page, specify the SQL Server connection, database, table(s), and view(s) for export.

Configure export options

On the Options page, customize table grid options for exported data:

• Header text color and background
• Row text color and background
• Border width and color

In the Preview section, check the appearance of the exported dataset.

For your convenience, you can make odd and even rows look different.

Customize data formats

The Data formats page has two tabs. On the first one, which is called Columns, select the columns for export and check/edit their aliases and data types.

On the second one, which is called Formats, you can change the default format settings for Date, Time, Date Time, Currency, Float, Integer, Boolean, and Null String, as well as select the required binary encoding from the dropdown list.

Select rows for export

Another notable page is Exported rows. When there is no need to export the entire table, you can select to export all rows, selected rows only, or a range of rows.

Configure errors handling

On the Errors handling page, configure how dbForge Studio should behave if it encounters an error during export. There are three possible options:

• Prompt you for an action
• Ignore all errors
• Abort at the first error

Also, you can select to create a log file with a report and set a path to it.

Complete the export

Once you configure all the required settings, click Export to start the operation. When your export is completed, on the Finish page, select one of the following actions:

• Show log file to open the log file
• Open result file to open the exported file
• Open result folder to open the folder containing the exported file
• Export more to start another export

Export data from the command line

dbForge Studio for SQL Server offers extensive automation options for regular SQL Server data export operations:

• Use the command line to automate exports
• Save data export settings as a command-line execution file (.bat) and run it whenever you need
• Schedule batch file execution via Windows Task Scheduler

Import data into a SQL Server database using the Data Import wizard

To import data in dbForge Studio, you need to open the Data Import wizard. There are several ways to do it.

• On the Start Page, select Data Pump > Import External Data.
• In Database Explorer, right-click the required database or server connection and select Tasks > Import Data.
• In Database Explorer, right-click the required table or view and select Import Data.
• On the menu bar, select Database > Tasks > Import Data.

Choose from the 10 supported SQL data import formats

When the wizard opens, you start with choosing a format. You can import data from the following formats: TXT, XLS, XLSX, MDB, JSON, XML, CSV, ODBC, DBF, and Google Sheets. There, you also select a file to import data from.

Import into a table

On the Destination page, select the destination for data import: namely, the SQL Server connection, database, schema, and a new or existing table.

Set import options

Next comes the Options page, where custom settings depend on the selected format. In the Preview section, you can conveniently double-check your configurations.

Configure data formats

The Data formats page has two tabs. On the first one, which is called Common Formats, you can change the default format settings for all columns:

• Null string
• Thousand separator
• Decimal separator
• Boolean
• Date and Time

On the second one, which is called Column Settings, you can specify format settings for each particular column.

Configure mapping

Column mapping is one of the core features that enables cross-database support within a single schema.

When you import data into a new table, the Studio automatically creates and maps all the columns.

When you import data into an existing table, the columns with the same names are mapped automatically, while all the remaining columns must be mapped manually.

Choose a data import mode

On the Modes page, choose the required data import mode:

• Append
• Update
• Append/Update
• Delete
• Repopulate

You can select to import data in a single transaction and/or in bulk.

Select output options

On the Output page, select one of the following options:

• Open the script in the internal editor
• Save the script to a file
• Import data directly to the database

If you select to save the script to a file, the Studio also allows adding a timestamp to the file name and selecting a folder to save your file to.

Manage import errors

On the Errors handling page, configure how dbForge Studio should behave if it encounters an error during import. There are three possible options:

• Prompt you for an action
• Ignore all errors
• Abort at the first error

Also, you can select to create a log file with a report and set a path to it.

Save reusable import templates

You can also save your import settings as templates for recurring scenarios. The flow is similar to that of data export: expand the Save menu in the bottom left corner of the wizard and select Save Template. Next, enter a name for your template file, pick a folder, and click Save. All saved templates will be available during your following import operations.

Finally, once you configure all the required settings, click Import to start the operation.

Import data from the command line

dbForge Studio delivers CLI-powered automation of SQL data import. You can do the following:

• Override the connection specified in the template
• Create a new table during import
• Import data using the template settings
• Specify the required file with the data to import
• Specify the table or view to import data from
• Specify the target table
• Specify the error handling behavior

Supported data export/import formats in dbForge Studio

Now, let's take a closer look at all the formats supported by dbForge Studio.

Video guide: How to export and import data in SQL Server

To make everything completely clear, check this video and see how dbForge Studio for SQL Server helps you save time and make your data migrations more efficient and convenient.

Conclusion

Now you know all about the easiest and most flexible way to import and export SQL Server data. And this is where we should note that dbForge Studio for SQL Server goes far beyond SQL import/export operations, covering the entire database lifecycle, from design and development to management, administration, and maintenance.

We should also mention the integrated context-aware dbForge AI Assistant, which will prove invaluable for your routine SQL development by generating, optimizing, explaining, and troubleshooting SQL code, as well as answering any SQL- and database-related questions.

We gladly invite you to download dbForge Studio for a free trial and explore everything it has to offer.

FAQ