How to insert data into a SQL table using the Insert new option in the Import mode

Applies to

ApexSQL Pump

Summary

This article explains how to insert data into SQL table in a SQL database using the Insert new import mode feature.

Description

ApexSQL Pump has different import data sources and 5 different import modes. In this article, the Insert new import mode will be used and it will show how to add new data to the list from the Excel file, in the existing SQL table which already has some data in it.

The first step is to setup the connection to SQL Server and chose the desired SQL database which will be used. In this example the Suppliers SQL database will be used:

ApexSQL Pump setup connection for the SQL Server and SQL database

The next step is to select the Import action under the Action tab and click the OK button:

Selecting the Import data action under the ApexSQL Pump's Action tab

When the SQL database is loaded, the main grid will be shown. For the purpose of this article the dbo.Employees SQL table will be used and the new data from the Excel file will be inserted into this existing SQL table:

ApexSQL Pump's import action in the main grid

In order to insert the new data from the Excel file, click the Manage button from the Home tab:

Initiating the Manage import process in ApexSQL Pump

Under the Manage import window, click the Add button under the Input source header. This will open the Add import source dialog in which the File option should be selected and the Browse button clicked to navigate to the Excel file, in this case dbo.Employees.xlsx, which will be loaded with the new data:

Adding import source to insert data in ApexSQL Pump

Before closing the Manage import window, the additional options, which are loaded on the right side, will be set. Document sheet is Employees, the Detect range automatically option is checked, the Import by Rows option is set and under the Header section the First row in range option is selected. The new data which needs to be insert into SQL table will be shown:

Input source using the Excel file to insert data into SQL table in ApexSQL Pump

The next step in the main grid is to select the SQL table, which in this example will be the dbo.Employees SQL table, and then go to the Settings panel which is by default on the right side of the application. Open the drop-down list from the Mapping section and click on the dbo.Employees.xlsx file:

Mapping drop-down list to select the Excel file to insert data into a SQL table in ApexSQL Pump

In the Settings panel, under the Table section, select the Update existing option and for Import mode select the Insert new to proceed further to insert data into the SQL table:

Table and Import mode settings in ApexSQL Pump

When the options are set in the Settings panel, the remaining thing before inserting data is to map the columns under the dbo.Employees SQL table. Click on each column and map it with the corresponding type in the drop-down list under the Column mapping cell:

Column mapping with the proper type from the Excel file in ApexSQL Pump

Before importing process is finished, current SQL data in the SSMS is shown from the selected SQL table in the SQL database, in this case dbo.Employees SQL table. In the screenshot below there are 50 records in the SQL table prior to insert data into SQL table from the Excel file is initiated:

SSMS query showing 50 records before importing process is initiated

To start the importing process in the ApexSQL Pump, click the Process button from the Home tab. In this example, the Column mapping will map each column in the selected SQL table and therefore in the Process window it will show that one SQL table is affected including 18 rows:

Initiating the process to insert data into a SQL table

  • Note: When the Column mapping is used, it doesn’t have to be used in a way to map each row in the SQL table. If the row is nullable, there is no need to be mapped and it can be excluded by leaving the None option.

When the Import button from the Process window is clicked, it will start the importing process. When the process is done, the View results dialog will be shown where affected tables and columns are shown, and in our case it’s the previously mentioned SQL table and its 18 columns:

View results dialog with affected table and columns

The final results can be seen in the next screenshot where insert data into SQL table is successfully done and newly records are added in the SQL table after 50 the existing ones:

SSMS results after inserting new data from the Excel file into the SQL table