SQL Bulk Copy Program in SQL - Server

To use the Bulk Copy Program (BCP) on SQL Server 2022 running on Azure Virtual Machines (VMs), follow these steps. BCP is a powerful command-line utility that allows fast import and export of data between a SQL Server table and a data file.
✅ Overview
BCP is ideal for:

• Importing large volumes of data into SQL Server from flat files (CSV, TXT).
• Exporting data from SQL Server to file formats for backup, transfer, or reporting.

When using SQL Server 2022 on Azure VMs, BCP works the same as it does on-premises, but with extra considerations for:

• Network security
• Storage locations (e.g., local disk or mounted Azure File Share)

🧱 Prerequisites

1. Install BCP Utility (if not already available):
   • Included with SQL Server tools or MS Command Line Utilities.
   • Verify with:
     

     bcp -v
     

2. Ensure the SQL Server is Accessible:
   • On your Azure VM, SQL Server must be listening on TCP port 1433.
   • Add an inbound NSG rule to allow traffic (if testing externally).
3. Authentication:
   • Use SQL authentication (-U and -P) or Windows authentication (-T if the VM is domain-joined).
4. Permissions:
   • Ensure the SQL login has BULK INSERT and table-level INSERT or SELECT permissions.

bcp YourDatabase.dbo.YourTable in "C:\data\importfile.csv" ^
   -S localhost -U sa -P YourPassword ^
   -c -t, -r\n

Flags Explained:

• YourDatabase.dbo.YourTable: Full name of the target table.
• in: Direction is import.
• -S: SQL Server instance (use localhost or public IP).
• -U / -P: SQL login credentials.
• -c: Character data type (default format).
• -t,: Field terminator is a comma (CSV).
• -r\n: Row terminator is newline.

📤 Export Example (SQL Table → Data File)

bcp YourDatabase.dbo.YourTable out "C:\data\exportfile.csv" ^
   -S localhost -U sa -P YourPassword ^
   -c -t, -r\n

This will export the table’s data into a CSV file.*
🔐 Security Tip for Azure VMs

• Avoid exposing your SQL Server to the public internet.
• If you must connect remotely, use Azure Bastion, VPN, or Just-in-Time VM access.
• Always encrypt your BCP file if it contains sensitive data.

💡 Advanced Use
* Export using a query:

  bcp "SELECT * FROM YourDatabase.dbo.YourTable WHERE status = 'active'" queryout "C:\data\filtered.csv" ^
     -S localhost -U sa -P YourPassword ^
     -c -t, -r\n
  

* Use format files for complex schemas:

  bcp YourDatabase.dbo.YourTable in "C:\data\importfile.csv" -f "C:\data\formatfile.fmt" ^
     -S localhost -U sa -P YourPassword
  

Here's a complete Azure CLI script to automate the BCP (Bulk Copy Program) import process into SQL Server 2022 running on an Azure VM. This script includes:

• Connecting to the Azure VM
• Uploading a CSV file to the VM
• Executing the bcp command remotely using a run command
• Cleanup steps (optional)

✅ Azure CLI Script to Automate BCP Import
🔧 Prerequisites

• SQL Server 2022 is installed and listening on localhost on your Azure VM
• The VM is running Windows OS
• You have a CSV file ready for upload (e.g., customers.csv)
• The SQL Server login (e.g., sa) and password are known
• bcp is available in the system path

📜 Script

#!/bin/bash

# Variables
RESOURCE_GROUP="MyResourceGroup"
VM_NAME="MySqlVm"
VM_ADMIN_USER="azureuser"
LOCAL_DATA_FILE="customers.csv"
REMOTE_DATA_FILE="C:\\data\\customers.csv"
SQL_DATABASE="SalesDB"
SQL_TABLE="dbo.Customers"
SQL_USER="sa"
SQL_PASSWORD="YourStrongPassword123"

• Step 1: Upload CSV to the Azure VM
  

  az vm run-command invoke \
    --resource-group "$RESOURCE_GROUP" \
    --name "$VM_NAME" \
    --command-id 'RunPowerShellScript' \
    --scripts "New-Item -Path 'C:\\data' -ItemType Directory -Force"
  
  az vm run-command invoke \
    --resource-group "$RESOURCE_GROUP" \
    --name "$VM_NAME" \
    --command-id 'RunPowerShellScript' \
    --scripts "Set-Content -Path '$REMOTE_DATA_FILE' -Value ''"  # Initialize empty file
  
  az vm extension set \
    --publisher Microsoft.Compute \
    --name CustomScriptExtension \
    --resource-group "$RESOURCE_GROUP" \
    --vm-name "$VM_NAME" \
    --settings "{\"fileUris\": [\"https://<your-storage-account>.blob.core.windows.net/<your-container>/$LOCAL_DATA_FILE\"], \"commandToExecute\": \"powershell -ExecutionPolicy Unrestricted -Command Move-Item -Path $LOCAL_DATA_FILE -Destination $REMOTE_DATA_FILE\"}"
  

• Step 2: Run the BCP import command on the VM
  

  az vm run-command invoke \
    --resource-group "$RESOURCE_GROUP" \
    --name "$VM_NAME" \
    --command-id 'RunPowerShellScript' \
    --scripts "bcp $SQL_DATABASE.$SQL_TABLE in \"$REMOTE_DATA_FILE\" -S localhost -U $SQL_USER -P $SQL_PASSWORD -c -t, -r\\n"
  
  echo "✅ BCP import executed."
  

• Replace the blob URL with your real Azure Blob Storage file path or implement az storage blob upload if you're using azcopy.
• CustomScriptExtension assumes you are moving files from Azure Blob to the VM. You can also SCP the file directly if the VM has SSH access.
• You can wrap this script inside a GitHub Actions workflow or Azure DevOps pipeline for CI/CD.

🔐 Security Considerations:
* Store passwords in Azure Key Vault and retrieve them using:

  SQL_PASSWORD=$(az keyvault secret show --vault-name "MyVault" --name "SqlPassword" --query value -o tsv)
  

Like the bulk insert statement, the bulk copy program (bcp) can be used to load data from a file into a table. However, unlike the bulk insert statement, bcp can also be used to extract data from a table. The parameters for the bcp program are similar to the bulk insert statement options. The following MouseOver illustrates the syntax of the bulk copy program.

When performing bulk copies, you should first know the following information:

1. When using a query, you can specify any valid SQL statement.
2. If you are using a query and it produces multiple result sets (either from calling a stored procedure, or having a compute by^[1] clause), the bcp will only extract the first result set.
3. If a data file is missing, the first parameter after the direction ( IN, OUT, FORMAT, QUERYOUT) is treated as the data file name.
4. The –E parameter has no effect if the file does not contain data for the identity column.
5. If –n, -c, -w, -6 , -N or –f are not specified, bcp will prompt you for format information about each column.
   If one of –n, -c, -w, -6 , -N or –f is specified, a \r will be used as the row terminator and a \t will be used as a field terminator.
6. In = Import data; out = export data; queryout = you are using a query to export data; format = generate a format file without extracting data--this must be used with the –f option.

bcp Sales.employee in empl.data 
-S WesternRegion –T -c

1. The example above loads data from the file empl.data into the Sales database.
2. The employee table is on the WesternRegion database server and uses integrated security.
3. This file has data stored in character format, with a tab between fields and a carriage return at the end of each record.

While bcp allows you to transfer data between SQL Server and files, often times you will want to move data between other data sources. The next lesson will introduce you to the basics of the data transformation services.

[1] Compute by: A TSQL extension that allows you to have summary rows intermixed with detail rows.