How to run data import from a SQL stored procedure
Last modified: June 5, 2025
To import data using a SQL stored procedure with dbForge Data Pump for SQL Server, you need to execute its command-line utility. However, SQL Server stored procedures do not have built-in support for running external command line utilities. As a workaround, you can use the xp_cmdshell
extended stored procedure to run the dbForge Data Pump command-line utility from within SQL Server.
Important
xp_cmdshell
can pose security risks and is often disabled by default. Make sure that proper security measures are in place before you enable and use the extended stored procedure.
This guide provides steps on how to run data import from a stored procedure that uses xp_cmdshell
.
Step 1: Create a template to import data
1. First, you need to configure the settings to be saved as a template. To start this process, in Object Explorer in your SQL Server Management Studio (SSMS), right-click a database, point to Data Pump, and click Import Data.
2. Go through all wizard pages and specify the necessary information and settings.
Note
The actual configuration of settings may differ, depending on the format. You will find the full workflow for each format in the corresponding topic:
3. After you have configured all necessary settings, click Save Template on the needed the wizard’s page.
Specify the name and the location of your template file in the Save As dialog and click Save.
Step 2: Create a batch file
In Notepad or any text editor, modify the script to import data as follows:
- Type a path to the datapump.com file located in the dbForge Data Pump for SQL Server installation folder on your PC.
- If you did not set a security password to create a database when configuring the import template in Import Wizard, add the
/password
parameter to the script.
"C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Data Pump for SQL Server\datapump.com" /dataimport /templatefile:<filepath> /password:<yourpassword>
-
The arguments should be substituted with your actual data:
/templatefile
: Full path to the template .dit file you created in the Data Import wizard, for example,C:\SampleFolder\SampleSubfolder\TemplateFile.dit.
/password
: The password you set for the data import.
Step 3: Enable the xp_cmdshell extended stored procedure
If the xp_cmdshell
extended stored procedure is disabled in your SQL Server instance, you can enable it by running the following commands in SSMS:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
Step 4: Create a stored procedure
Next, you need to create a stored procedure that uses xp_cmdshell to execute the batch file. Replace C:\SampleFolder\SampleSubfolder\BatchFile.bat
in the following query with the actual path to your batch file:
CREATE PROCEDURE dbo.RunDataPumpImport
AS
BEGIN
EXEC xp_cmdshell 'C:\SampleFolder\SampleSubfolder\BatchFile.bat';
END;
Step 5: Execute the stored procedure
Finally, execute the created stored procedure to run the data import:
EXEC dbo.RunDataPumpImport;
After executing the stored procedure, retrieve data from the target table to verify that the necessary records have been imported successfully:
For more information about using the command line for data import with dbForge Data Pump, see Importing data from the command line.
For more information about scheduling data imports, see Scheduling data import.