How to Export Data to Flat File with BCP Utility and Import data with Bulk Insert

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 TABLE [dbo].[Orders_Monthly_Temp_Table](
    [Row] [varchar](200) NOT NULL
) ON [PRIMARY]

Create a script for Export_Config

CREATE TABLE [dbo].[Export_Config](
    [Exp_Id] [int] IDENTITY(1,1) NOT NULL,
    [ShareFolder] [varchar](200) NOT NULL,
    [FileType] [varchar](5) NOT NULL,
    [Delimiter] [char](1) NOT NULL,

 CONSTRAINT [PK_Export_Config] PRIMARY KEY CLUSTERED 
(
    [Exp_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA]
GO

Insert data into Export_Config

SET IDENTITY_INSERT [dbo].[Export_Config] ON 
GO
INSERT [dbo].[Export_Config] ([Exp_Id], [ShareFolder], [FileType], [Delimiter]) VALUES (1, N'\\AASHREEPC\FileServer\OrdersMonthly', N'.txt', N'|')
GO
SET IDENTITY_INSERT [dbo].[Export_Config] OFF
GO

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.

CREATE PROCEDURE [dbo].[Orders_Monthly_Report] 
    @Month INT = NULL
    ,@Year INT = NULL
    ,@FolderPath VARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY

Parameter Validation

--#region Parametes validation
        IF NULLIF(@Month, '') IS NULL 
        BEGIN
            SELECT @Month = DATEPART(mm, DATEADD(month, - 1, GETDATE()))

            IF (@Month = 12) –
            BEGIN
                SELECT @Year = DATEPART(Year, GETDATE()) - 1
            END
        END

        IF NULLIF(@Year, '') IS NULL
        BEGIN
            SELECT @Year = DATEPART(Year, GETDATE())
        END

        IF NULLIF(@FolderPath, '') IS NULL  
        BEGIN
            --SELECT @FolderPath = '\\AASHREEPC\FileServer'
            SELECT 'ERROR FolderPath must be specified.'
            RETURN;
        END        
--#endregion Parameters validation

Get the configuration from the export table

DECLARE @ExportPath VARCHAR(200)
            ,@Delimiter CHAR(1)
            ,@FileType VARCHAR(5)

        SELECT @ExportPath = TRIM(ShareFolder)
            ,@FileType = TRIM(FileType)
            ,@Delimiter = TRIM(Delimiter)
        FROM dbo.Export_Config

Getting the start date and end date of the month

DECLARE @MonthStartDate DATETIME = DATEADD(month, @Month - 1, DATEADD(year, @Year - 1900, 0))
            ,@MonthEndDate DATETIME = DATEADD(day, - 1, DATEADD(month, @Month, DATEADD(year, @Year - 1900, 0)))

Check and Create the temporary table for report data/result
IF NOT EXISTS (
                SELECT *
                FROM sys.objects
                WHERE object_id = OBJECT_ID(N'[dbo].[Orders_Monthly_Temp_Table]')
                    AND type IN (N'U')
                )
        BEGIN
            CREATE TABLE [dbo].Orders_Monthly_Temp_Table ([Row] [varchar](200) NOT NULL) ON [PRIMARY]
        END

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

TRUNCATE TABLE Orders_Monthly_Temp_Table
INSERT INTO Orders_Monthly_Temp_Table
        SELECT CAST([OrderID] AS VARCHAR(10)) + ' | ' + CAST(c.[CustomerName] AS VARCHAR(50)) + ' | ' + CAST(p.[FullName] AS VARCHAR(50)) + ' | ' + ISNULL(CAST([PickedByPersonID] AS VARCHAR(4)), '') + ' | ' + CAST(p.[FullName] AS VARCHAR(20)) + ' | ' + ISNULL(CAST([BackorderOrderID] AS VARCHAR(4)), '') + ' | ' + CAST([OrderDate] AS VARCHAR(20)) + ' | ' + CAST([ExpectedDeliveryDate] AS VARCHAR(20)) + ' | ' + CAST([CustomerPurchaseOrderNumber] AS VARCHAR(10)) + ' | ' + CAST([IsUndersupplyBackordered] AS VARCHAR(4)) + ' | ' + ISNULL(CAST([Comments] AS VARCHAR(50)), '') + ' | ' + ISNULL(CAST([DeliveryInstructions] AS VARCHAR(50)), '') + ' | ' + ISNULL(CAST([InternalComments] AS VARCHAR(50)), '') + ' | ' + CAST([PickingCompletedWhen] AS VARCHAR(20)) + ' | ' + CAST(o.[LastEditedBy] AS VARCHAR(4)) + ' | ' + CAST([LastEditedWhen] AS VARCHAR(20)) AS Row
        FROM [WideWorldImporters].[Sales].[Orders] o
        INNER JOIN [Sales].[Customers] c ON o.[CustomerID] = c.[CustomerID]
        INNER JOIN [Application].[People] p ON o.[SalespersonPersonID] = p.[PersonID]
        WHERE OrderDate BETWEEN @MonthStartDate
                AND @MonthEndDate

Code to export the data to a flat file

Create the folder if not exists Using SQL xp_create_subdir

DECLARE @sql VARCHAR(8000)
            ,@FilePath VARCHAR(200)
            ,@Query VARCHAR(100)
        DECLARE @file_results TABLE (
            file_exists INT
            ,file_is_a_directory INT
            ,parent_directory_exists INT
            )

        SET @FolderPath = @FolderPath + '\' + CAST(@Year AS VARCHAR(10)) + '\' + CAST(@Month AS VARCHAR(10)) + '\'

        INSERT INTO @file_results
        EXEC MASTER.dbo.xp_fileexist @FolderPath

        IF NOT EXISTS (
                SELECT 1
                FROM @file_results
                WHERE file_is_a_directory = 1
                )
            EXEC MASTER.dbo.xp_create_subdir @FolderPath

Creating the file in the shared folder

SET @FilePath = '"' + @FolderPath + '' + 'Orders_Monthly' + '_' + (
                SELECT Format(GETDATE(), N'yyyyMMddHHmmss')
                ) + '.txt"'
        SET @Query = '"SELECT * from ' + (
                SELECT DB_NAME()
                ) + '.dbo.Orders_Monthly_Temp_Table"'

        DECLARE @exe_path10 VARCHAR(200) = ' cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130 & '

        SELECT @sql = @exe_path10 + ' bcp.exe ' + @Query + ' queryout ' + @FilePath + '  -T -c -q -t0x7c -r\n ' --+ @@servername

        EXEC master..xp_cmdshell @sql
    END TRY

    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_STATE() AS ErrorState
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    SET NOCOUNT OFF;
END

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

[table id=58 /]

Executing the procedure

DECLARE    @return_value int
EXEC    @return_value = [dbo].[Exp_Orders_Monthly_Report]
        @Month = NULL,
        @Year = NULL,
        @FolderPath = NULL
SELECT    'Return Value' = @return_value
GO

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

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

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.

CREATE TABLE [dbo].[Orders_Monthly](
    [OrderID] [int] NOT NULL,
    [CustomerName] [varchar](50) NOT NULL,
    [SalespersonPersonName] [varchar](50) NOT NULL,
    [PickedByPersonName] [varchar](50) NULL,
    [ContactPersonName] [varchar](50) NOT NULL,
    [BackorderOrderID] [varchar](4) NULL,
    [OrderDate] [date] NOT NULL,
    [ExpectedDeliveryDate] [date] NOT NULL,
    [CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
    [IsUndersupplyBackordered] [bit] NOT NULL,
    [Comments] [nvarchar](max) NULL,
    [DeliveryInstructions] [nvarchar](max) NULL,
    [InternalComments] [nvarchar](max) NULL,
    [PickingCompletedWhen] [datetime2](7) NULL,
    [LastEditedBy] [int] NOT NULL,
    [LastEditedWhen] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Orders_Monthly] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA] TEXTIMAGE_ON [USERDATA]
GO

CREATE TABLE [dbo].[Import_Config](
    [Exp_Id] [int] IDENTITY(1,1) NOT NULL,
    [ShareFolder] [nchar](200) NOT NULL,
    [FileType] [varchar](5) NOT NULL,
    [Delimiter] [char](1) NOT NULL
) ON [USERDATA]
GO

Insert data into Import_Config

SET IDENTITY_INSERT [dbo].[Import_Config] ON 
GO
INSERT [dbo].[Import_Config] ([Exp_Id], [ShareFolder], [FileType], [Delimiter]) VALUES (1, N'\\AASHREEPC\FileServer\OrdersMonthly', N'.txt', N'|')
GO
SET IDENTITY_INSERT [dbo].[Import_Config] OFF
GO

Stored procedure Creation & Parameters

Same as in the export Stored procedure.

CREATE PROCEDURE [dbo].[Imp_Orders_Monthly_Report] @Month INT = NULL
    ,@Year INT = NULL
    ,@FolderPath VARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
Get the configuration from the import table
DECLARE @ImportPath VARCHAR(200)
            ,@Delimiter CHAR(1)
            ,@FileType VARCHAR(5)
            ,@FilePath VARCHAR(200)

        SELECT @ImportPath = TRIM(ShareFolder)
            ,@FileType = TRIM(FileType)
            ,@Delimiter = TRIM(Delimiter)
        FROM dbo.Import_Config

Parameter Validation

Same as in the export Stored procedure.

SET @FolderPath = @ImportPath + '\' + CAST(@Year AS VARCHAR(10)) + '\' + CAST(@Month AS VARCHAR(10)) + '\'
            END
            ELSE
            BEGIN
                --SELECT @FolderPath = '\\AASHREEPC\FileServer\OrdersMonthly'
                SELECT 'ERROR FolderPath must be specified.'

                RETURN;
            END
        END

        --#endregion Parametes validation

Check if the file exists or not

CREATE TABLE #File (
            FileName SYSNAME
            ,Depth TINYINT
            ,IsFile TINYINT
            );

        INSERT INTO #File (
            FileName
            ,Depth
            ,IsFile
            )
        EXEC xp_DirTree @FolderPath
            ,1
            ,1

        SELECT TOP 1 @FilePath = @FolderPath + '\' + FileName
        FROM #File
        ORDER BY FileName DESC;
        
        IF NULLIF((SELECT TOP 1 FileName FROM #File ORDER BY FileName DESC), '') IS NULL
        BEGIN
                SELECT 'ERROR import File does not exists'
                RETURN;
        END

        DROP TABLE #File
Import the data from the shared folder using Bulk Insert
DECLARE @SQL_BULK VARCHAR(MAX)
        DecLare @Errorlog varchar (Max) = @FolderPath + '\Error.log'
        SET @SQL_BULK = 'BULK
            INSERT [Orders_Monthly]
            FROM ''' + @FilePath + '''
            WITH
            (
                DATAFILETYPE = ''char''
                ,BATCHSIZE = 50000
                ,CODEPAGE = ''RAW''
                ,FIRSTROW = 1
                ,FIELDTERMINATOR = '''+@Delimiter+'''
                ,ROWTERMINATOR = ''\n''
                ,KEEPNULLS
                ,ERRORFILE = '''+ @Errorlog + '''
                ,MAXERRORS = 20000
                ,TABLOCK
                )'

            EXEC (@SQL_BULK)
END TRY

    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_STATE() AS ErrorState
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    SET NOCOUNT OFF;
END

Executing the procedure

DECLARE    @return_value int
EXEC    @return_value = [dbo].[Imp_Orders_Monthly_Report]
        @Month = NULL,
        @Year = NULL,
        @FolderPath = NULL
    SELECT    'Return Value' = @return_value
GO

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.

USE [WideWorldImporters]
GO
DECLARE    @return_value int+
EXEC    @return_value = [dbo].[Exp_Orders_Monthly_Report]
        @Month = NULL,
        @Year = NULL,
        @FolderPath = NULL
SELECT    'Return Value' = @return_value
GO

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.