Applies to
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:
The next step is to select the Import action under the Action tab and click the OK button:
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:
In order to insert the new data from the Excel file, click the Manage button from the Home tab:
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:
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:
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:
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:
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:
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:
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:
-
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:
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:
