How to import data to a new SQL Server table

The guide describes how to import data into a new SQL Server table using the Data Import wizard available in dbForge Data Pump for SQL Server.

To import data to a new table

1. In Object Explorer, right-click a database to which you want to import data and select Data Pump > Import Data.

2. In the Data Import Wizard that opens, select a file format and the file of source data to import, and then click Next.

3. On the Destination tab, specify a target SQL Server connection and a database, select New table, and enter the name of the table to import the data to. Then, click Next.

Select a new table

4. On the Options tab, specify additional options (depending on the file format you chose) to customize the import and click Next.

5. On the Data formats tab, set data formats and column settings for the source data and click Next.

Optional: By default, the date format is selected. If you want to change the format, clear the Autodetect Date and Time format check box.

6. On the Mapping tab, Data Pump automatically creates and maps all the columns in the target database to the source columns. The target columns are displayed on top and the source columns - at the bottom of the wizard page. Select the Key check box for a column with a primary key; otherwise, some of import modes on the Modes tab will be disabled. Then, click Next.

To remap columns, click the Source column fields and select the required columns from the drop-down list.

On the toolbar, you can perform the following actions:

Mapping tab

  • Add a new column by clicking New Column. In the Column Properties dialog that opens, set column properties, such as a name, a data type, whether it contains NULLs or is a primary key, etc. To save the changes, click OK, or to discard the changes, click Cancel.

Set column properties

  • Delete the column by clicking Delete.

  • Edit the column by clicking Edit or double-clicking the column on the upper grid.

  • Move columns up Move up the column or down Move down the column

  • Add column mapping by clicking Fill Mapping.

  • Cancel the mapping of all columns, click Clear Mappings.

7. On the Modes tab, select an import mode to define how Data Pump should import the data and click Next.

Select an import mode

On the Modes tab, you can also do the following:

  • Select the Use a single transaction check box to wrap INSERT statements in a single transaction and, thus, to keep data consistency.
  • Select the Use bulk insert to reduce the number of statements and speed up the import. However, note that this may affect an error handling mechanism.

8. On the Output tab, select one of the following output options to manage the import script and click Next:

  • Open the data import script in the internal editor: The script will be opened in a new SQL document of the Data Pump tool where you can add custom add or execute the script by clicking Execute on the toolbar or pressing F5.
  • Save the data import script to a file: The script will be saved locally on your PC. When selected, specify a path to the folder you want to save the script to.

If you want to add date and time to the file name, select the Add timestamp to the file name check box.

If you want to open the script in the internal editor after saving, select the Open in the internal editor check box.

  • Import data directly to the database: The script will be executed without your review.

Select an output script

9. On the Error handling tab, select how Data Pump should handle errors during import and whether you want to get a log file with details about the import session.

You can select one of the following options:

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

10. To import data, click Import and see the import progress. Data Pump will notify you whether the import was completed successfully or failed.

If you want to open the log file, click Show log file. If you want to start another data import, click Import more.

Note

You can save the import settings as a template for future use. To save import settings, click Save Template on any wizard tab. Next time you should only select a template and specify a location of the source data - all the settings will be already set. For more information, see How to use data import templates.

11. To finish the import and close the wizard, click Finish.