Exporting Data to Flat File with BCP Utility and Importing data with Bulk Insert

Total: 21 Average: 4

The BCP (Bulk Copy Program) utility in SQL Server allows database administrators to import data into a table and export data from a table into a flat file. The BCP utility also supports various features that facilitate the process of exporting and importing of the bulk data.

Now let’s get started with a business scenario.

Business Scenario

Let’s say, we need to share a monthly report in the specific format to a client in a secured shared location like SFTS, i.e. at the beginning of every month, we need to send the file to a client for the previous month. In this scenario, we’ll try to create the stored procedure to generate data and export that data to the flat file (.txt or .csv).

How to import and export the SQL data?

There are several ways to do this:

  • Using SSMS, Run the Query in the Query window and export or SQL Server import & export wizard.
  • Using SSIS – Creating a package using SSDT.
  • Using SSRS.
  • Using C# – Create console or win Application to export.
  • BCP utility.
  • etc.

What is BCP Utility?

BCP (Bulk copy program) utility is a command-line utility to copy data between an instance of MS SQL Server and a data file in a user-specified format. We can export and import large amounts of data in and out of the SQL Server databases quickly and easily.

The BCP utility performs the following tasks:

  • Bulk data export from a SQL Server table into a data file.
  • Bulk data export from a query/Stored Procedure.
  • Bulk data import from a data file into a SQL Server table.
  • Generation of the format files.

You can find more details about BCP Utility here.

Environment used

  • SQL Server 2017 Developer Edition
  • SQL server 2017 Management studio
  • Wide World Importers sample database v1.0
  • BCP Utility

How to Export data to a flat file

Create a Stored procedure to generate the monthly report data.

First, create the dependent objects for the export Stored procedure.

So we have to create the following tables:

  • The Orders_Monthly_Temp_Table table: this temporary table is used to store the monthly orders data in a specific format to export that to text file i.e. in our case concatenating all the columns into one row with delimiter “|”.
  • The Export_Config table: this table is used to store export configurations i.e. shared folder path, flat file type, delimiter.

Create a script for Orders_Monthly_Temp_Table

Create a script for Export_Config

Create script for Export_Config

Insert data into Export_Config

Stored procedure Creation & Parameters

  • Here the year and month parameters are optional.
  • If a month is not specified it takes the previous month and if the month is 12 we have to take the previous year, because if we are generating the report in jan’2019 for Dec’2018.
  • If a year is not specified, it takes the current year And Folder path is Mandatory.

Parameter Validation

Get the configuration from the export table

Getting the start date and end date of the month

Insert the data into the temp table in specific format i.e. in this case “| – pipe symbol separated”

Code to export the data to a flat file

Create the folder if not exists Using SQL xp_create_subdir

Creating the file in the shared folder

Change your directory context to the folder where BPC Utility is located

SQL Server VersionsLocation
SQL Server 2012C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn
SQL Server 2014C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\120\Tools\Binn
SQL Server 2015C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn
SQL Server 2017C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\140\Tools\Binn

Executing the procedure

Output

Destination folder

Actual Flat file (.txt/.cvs)

Shared folder should have Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”

Right-click the file or folder you want to set permissions → Click Properties → Click the Security tab. → Click Edit → Click Add → Type NT SERVICE\MSSQLSERVER in the object name box. (don’t click “Check Names” – if you click Check Names it can happen that you get an error ‘An object named “NT SERVICE\MSSQLSERVER” cannot be found.) → Click OK → choose the MSSQLSERVER account → Add permissions (Full control) that are needed to the MSSQLSERVER account:

Enable ‘xp_cmdshell’ SQL Server

How to Import Data from Flat file

In this example, we are using Bulk Insert to import data from the file. We can also use Openrowset etc.

Create a Stored procedure to Import the data from a flat file in the Shared folder.

First, create the dependent objects for the import Stored procedure.

So we have to create the following tables

  • The Orders_Monthly table: this table is used to store the monthly orders data from the flat file.
  • The Import_Config table: this table is used to store import configurations i.e. shared folder path, flat file type, delimiter.

 

Insert data into Import_Config

Stored procedure Creation & Parameters

Same as in the export Stored procedure.

Parameter Validation

Same as in the export Stored procedure.

Check if the file exists or not

Executing the procedure

Output

Verification

Automating the process:

For running the export and import process automatically in a scheduled time. let’s say we need to run the export on the first day of the month at 12:00 am of the month for the last month report and run the import later. So we need to create the SQL Job for that.

Steps to create the SQL Job for Export and import.

  • Open MS SQL Server Management Studio →
  • and you should have the “SQL Server Agent” →
  • Expand the “SQL Server Agent” on Object Explorer. →
  • Right-click  JOB and select the “New Job…” →
  • You can see the “New Job” window and enter the name = “Orders_Monthly_Export” &Description

Then go the Steps tab → Click New Button in the bottom → a new Job Steps window opens → Enter the Name = “execute [Exp_Orders_Monthly_Report] SP” and Type = “Transact-SQL Script (T-SQL)” → Paste the following script in the Command text area and Click OK.

 

Then Go to the Schedule tab → Click New Button at the bottom → a new job Schedule window opens. Enter the Name = “Order Monthly Schedule” and enter the following details and Click OK → Again Click OK in the New Job window.

The job would be created successfully.

Test the SQL Job:

Delete all the files in the Shared folder for testing.

To run the job manually for testing: Right Click on the Newly created Job → Click ‘Start Job at step..’ and we can see the job running

We can see the file is created in the Shared folder.

Note: Kindly follow the above steps to create the SQL job (Orders_Monthly_Import) for Import too.

I hope that now you have a better understanding of how to use the BCP utility.

 

Useful tool:

dbForge Data Pump – an SSMS add-in for filling SQL databases with external source data and migrating data between systems.

Amarendra Babu

Amarendra Babu L is a technical content writer. He always looks for new ways to explore the latest software technology innovations.
Amarendra Babu