Skip to content

SQL Server Bulk Copy Program (BCP)

    SQL Server Bulk Copy Program (BCP) utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) to perform the following tasks:

    • The bcp utility can export data from a SQL Server table to a data file
    • The bcp utility can import data from a data file to a SQL Server table
    • Generates format files

    The bcp utility is accessed by the bcp command or by using a batch file

    You can use a batch file to set up a schedule using BCP

    BCP is case sensitive

    User must be aware of the data types and lengths of the table columns

    The bcp command provides switches that you use to specify the data type of the data file and other information

    SQL Server Bulk Copy Program

    You will receive the number of rows copied, the network packet size and the speed to copy the rows per second.

    If everything is OK, the file will be created:

    You can open the file and check the data:

    Bulk Copy Program

    Syntax and switches

    bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
    
       [-a packet_size]
       [-b batch_size]
       [-c]
       [-C { ACP | OEM | RAW | code_page } ]
       [-d database_name]
       [-e err_file]
       [-E]
       [-f format_file]
       [-F first_row]
       [-h"hint [,...n]"] 
       [-i input_file]
       [-k]
       [-K application_intent]
       [-L last_row]
       [-m max_errors]
       [-n]
       [-N]
       [-o output_file]
       [-P password]
       [-q]
       [-r row_term]
       [-R]
       [-S [server_name[\instance_name]]
       [-t field_term]
       [-T]
       [-U login_id]
       [-v]
       [-V (80 | 90 | 100 | 110)]
       [-w]
       [-x]
       /?
    

    Examples

    1. Using bcp to EXPORT data from SQL table to flat file using OUT
    
    --Syntax: 
    
    bcp databasename.schema.tablename out "to the path" -T (trusted credentials) -c (char datatype)
    
    --the following command will export data FROM sql to flat file
    bcp sql2.dbo.people3 out "c:\bcps\people3.txt" -T -c
    
    --the following command will import data FROM flat file sql to
    bcp sql2.dbo.people4 in "c:\bcps\people3.txt" -T -c
    
    --create table peopel4 in SQL2 database to receive data from file
    
    USE [SQL2]
    GO
    
    CREATE TABLE [dbo].[PEOPLE4](
    	[PeopleID] [int] NOT NULL,
    	[Fname] [varchar](20) NULL,
    	[Lname] [varchar](20) NULL,
    	[Address1] [varchar](100) NULL,
    	[City] [varchar](50) NULL,
    	[State] [varchar](50) NULL,
    	[Zip] [varchar](10) NULL,
    	[Country] [varchar](50) NULL,
    	[Phone] [varchar](20) NULL
    ) ON [PRIMARY]
    
    GO
    
    
    --2. Send data from sql to file using a query
    
    bcp "select top 100 * from sql2.dbo.people3" queryout "c:\bcps\query.txt" -T -c
    

    Run bcp using PowerShell

    PowerShell is a powerful tool to automate tasks using scripts and the command line. You can also run bcp using PowerShell, which is included with Windows and it can be also installed on Linux and Mac.

    In PowerShell create variables to store the database name, schema, table and output path:

     $db = “adventureworks”$schema = “person”$table = “person”$path = “C:\sql\powershell.txt” 

    Store the bcp command line with the parameters in another parameter:

     $psCommand = “bcp $($db).$($schema).$($table) out $path -T -c” 

    Use the Invoke-Expression to call the variable with the bcp commands:

    Invoke-Expression $psCommand 

    PowerShell will import the files in the powershell.txt file:

    Bulk Copy Program

    Run bcp on SSIS

    It is not a common practice to run bcp on SSIS because SSIS contains tasks to import and export data. You can use the Data Flow task to create customized tasks or maybe use the Bulk Insert task to import data to SQL Server.

    However, there are some scenarios where you can invoke bcp in SSIS. For example, if you already have some command lines in bcp and you just want to invoke them. Another case is when you are adept at the command line and you already have some batch files ready and you just want to invoke them.

    Open the SSDT and create a New Project.

    In projects select Integration Services Project:

    Drag and drop the Execute Process Task to the design pane:

    In executable, specify the path of the bcp file:

    C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

    In arguments specify the bcp arguments to export data from a SQL Server table to a text file named: ssis.bcp

    adventureworks.person.person out c:\sql\ssis.bcp -c -T -S WIN-355CHQ0E524

    Your SSIS tasks will look like this:

    Right click the tasks and select Execute task:

    If everything is OK, the task will be like this and a file named ssis.bcp will be created with the data exported: