bcp

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-01 helpful for verify / EXEC usp_*; not required to start
Goal: Training expansion of the original lean deep-dive — choose bcp vs BULK INSERT vs OPENROWSET vs SqlBulkCopy vs Invoke-Sqlcmd loops correctly, then run the labs and recipes until inventory dumps and vendor CSVs → staging → proc → Blazor are muscle memory for Rick’s AD/Windows/PowerShell stack.

Cross-links (do not duplicate): SQL-CLI-01 sqlcmd / go-sqlcmd · SQL-CLI-03 SqlServer module / Write-SqlTableData · SQL-CLI-06 auth cheat sheet · SQL-CLI-07 PS→SQL→Blazor patterns

This page owns the bcp client: install, PATH, directions (in / out / queryout / format), practical flags, format files, and PowerShell orchestration that feeds staging tables. Auth matrix and Encrypt/TSC across every tool live in CLI-06. Object shaping / Write-SqlTableData / SqlBulkCopy live in CLI-03. End-to-end Collect→Stage→Load→EXEC→Blazor lives in CLI-07. Verify hops use CLI-01.

Learning outcomes

After this page you can:

• Pick bcp, BULK INSERT, OPENROWSET(BULK), SqlBulkCopy, or Invoke-Sqlcmd row loops for a given load without guessing.
• Install / locate bcp.exe from the same Client SDK as classic sqlcmd, prove which binary PATH will run, and pin a full path in Agent jobs.
• Connect with Windows Integrated (-T), SQL auth, and Entra (-G) using documented flags only; know when -Y / -u (bcp 18+) matter.
• Drive all four directions: in, out, queryout, format nul — and pick -c / -w / -n / -N / -f correctly.
• Generate and edit format files (non-XML vs XML); skip identity / remap vendor columns; keep format files in deploy folders.
• Orchestrate inventory dumps and vendor CSVs in PowerShell: loops over servers/files, $LASTEXITCODE, stage → EXEC usp_*, verify with sqlcmd.
• Avoid the usual silent-poison cases: code page / UTF-8 vs format-file collation, empty string ↔ NULL, triggers off by default, untrusted CHECK/FK, Azure path / firewall limits.
• Paste any of the recipes below on a jump box and get a real result.

1. Decision map (five tools)

Rick’s typical loads: inventory extracts, vendor CSV drops, staging → EXEC dbo.usp_* → Blazor reads procs/views. Pick the tool by where the file lives and who owns the process.

Rick’s stack default

Ops / Agent / scheduled extract-import on Windows box  → bcp + PowerShell + $LASTEXITCODE
File already next to SQL (T-SQL job / proc)            → BULK INSERT
Blazor Server / .NET worker already holding DataTable  → SqlBulkCopy / Write-SqlTableData (CLI-03)
Verify row counts / run post-load procs                → sqlcmd / go-sqlcmd (CLI-01)
Need objects → JSON for Blazor shaping                 → Invoke-Sqlcmd (CLI-03) — not for bulk load
Never row-by-row INSERT loops for inventory dumps      → use bcp / BULK INSERT / SqlBulkCopy
Kerberos / Encrypt / full auth matrix                  → CLI-06
Collect → Stage → Load → EXEC → Blazor                 → CLI-07

Rule of thumb: if the next hop is a file on the client, use bcp. If the next hop is a T-SQL job that can see the file, use BULK INSERT. If the next hop is already a .NET DataTable/IDataReader, use SqlBulkCopy. If you only need to verify / EXEC, use sqlcmd (CLI-01).

2. Install lab (Windows)

Same Client SDK ODBC Tools\Binn family as classic sqlcmd (see SQL-CLI-01 §2). Do this on the jump box before you trust any script.

2.1 Sources

Typical paths (version folders vary — 170 vs 180):

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

2.2 Versioning (Learn, sql-server-ver17 / researched 2026-09)

OS floor from Learn: Windows 10 / Windows Server 2016 or later.

where.exe bcp
bcp -v
# If multiple: PATH wins. Prefer the ODBC\180 (or newest) Binn that matches your driver.
Get-ChildItem 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\*\Tools\Binn\bcp.exe'

Download hub: Download and install bcp.

3. Auth lab

Flags here. The decision matrix (when Integrated vs SQL vs Entra, plus Kerberos/double-hop, Encrypt across every tool) is SQL-CLI-06. Do not copy that sheet into scripts — link it.

3.1 Windows Integrated (on-prem)

bcp "AppDb.dbo.Inventory" out .\inv.bcp -S sql01.contoso.local -T -n

-T = trusted connection (Windows Integrated). Do not use -T against Azure SQL Database / Synapse — Learn: not supported. For Entra, use -G.

3.2 SQL auth

# Prefer prompt (omit -P) or SecretStore / env — never -P on shared cmdline / Agent args / transcripts
bcp dbo.InventoryStaging in .\vendor.csv -S sql01 -d AppDb -U deploy_user -f .\fmt\InventoryStaging.fmt -F 2
# bcp prompts for password when -U is set and -P is omitted

If you must automate SQL auth: SecretManagement / Key Vault → env var for the process only → scrub after. Same discipline as sqlcmd’s SQLCMDPASSWORD (CLI-01) — bcp itself does not read SQLCMDPASSWORD.

3.3 Entra ID / Azure

-G requires bcp ≥ 14.0.3008.27. Applies to Azure SQL Database, Azure SQL MI, Synapse, SQL database in Fabric, and SQL Server 2022 (16.x)+.

# Entra interactive (Windows MFA)
bcp dbo.Inventory out .\inv.bcp -S contoso.database.windows.net -d AppDb -G -U 'rick@contoso.com' -w -l 60

# Entra integrated (federated Windows identity)
bcp dbo.Inventory out .\inv.bcp -S contoso.database.windows.net -d AppDb -G -w -l 60

Azure login timeout: bump -l (default 15s) — 30–60 is common. Firewall / private endpoint must allow the jump-box IP. Entra principal must exist in-DB (CREATE USER … FROM EXTERNAL PROVIDER) — DBA step, not a flag.

Full Entra matrix across tools: CLI-06. Auth deep-dive for bcp: Authenticate with Microsoft Entra ID in bcp.

3.4 Encrypt / TrustServerCertificate (bcp 18+)

SQL Server 2025 / bcp 18: omit -Y → -Ym (mandatory). Lab boxes with self-signed certs usually need -u. Prefer real certs in prod.

Older bcp without -u/-Y: use a DSN (-D -S MyDsn) with TrustServerCertificate=yes / encrypt options the driver supports.

Cross-tool Encrypt / TSC matrix: CLI-06.

3.5 DSN, AG, naming

Server forms: -S sql01, -S sql01\INST, -S tcp:sql01,1433, -S contoso.database.windows.net.

Database: prefer either three-part name AppDb.dbo.Inventory or -d AppDb — not both (error).

Quoted identifiers / weird names: -q and quote the three-part name:

bcp "AppDb.dbo.My Table" out .\t.bcp -S sql01 -T -w -q

-q does not apply to values passed to -d.

4. Directions deep dive

bcp {table|view|"query"} { in | out | queryout | format nul } {data_file|nul} [options]

Data representation (pick once and stick)

Pick once: native (-n / -N) for internal hops; -w or format + -t, for vendor CSV; never mix a native file with a -c import.

queryout rules (Learn)

• Double-quote the query; single-quote literals inside.
• Only the first result set is copied; later sets are ignored.
• A proc is fine only if all referenced tables exist at statement metadata time — temp tables created inside the proc are invisible to bcp → land results in a real/staging table first, then out.

Path / size limits

• Data-file path: 1–255 characters (Learn).
• Max rows in a data file: 2^63 − 1.
• out overwrites an existing file — no prompt.

5. Flags cheat sheet (practical groups)

Only what ops actually touch. Full list: bcp utility (Learn, ver17).

Connection

Data shape

Performance

-b and -h "ROWS_PER_BATCH=…" are mutually exclusive. Use -b when you want bcp to control batching; use ROWS_PER_BATCH to hint the optimizer when sending as a single transaction.

Errors / logging

Exit code: 0 success; non-zero failure — always check in PowerShell ($LASTEXITCODE). Native exe does not throw.

6. Format files lab

When you need them

• CSV/pipe/fixed layout ≠ table order or types
• Skip identity / computed / columns absent from file
• Remap vendor column order
• Repeatable deploys (check format file into repo next to scripts)

Non-XML vs XML

Generally interchangeable. Reader bcp version must be ≥ creator version (e.g. 2025/17.0 bcp can read a 2022/16.0 file; 2022 bcp cannot read a 17.0 file).

Generate

# Non-XML (.fmt) — portable, easy to edit terminators
bcp AppDb.dbo.InventoryStaging format nul -c -t, -r\n -f .\fmt\InventoryStaging.fmt -S sql01 -d AppDb -T

# XML (.xml) — Learn-recommended readability; Windows (-x)
bcp AppDb.dbo.InventoryStaging format nul -c -x -t, -f .\fmt\InventoryStaging.xml -S sql01 -d AppDb -T

format always requires -f. Data path must be nul.

Edit for CSV (typical inventory / vendor drop)

1. Generate with -c -t,.
2. Confirm last field terminator is \r\n (Windows files) or \n (Unix).
3. Set server column ordinal to 0 to skip a table column (identity, audit cols) — non-XML column 6 is the server column order.
4. Remap file field order by changing which FIELD maps to which COLUMN (XML) or reordering / renumbering (non-XML).
5. Keep .fmt/.xml with deploy scripts under .\fmt\ or Deploy\Bcp\.
6. If importing UTF-8 with -C65001: format-file collation/code-page stamps override -C65001 — strip collation from .fmt columns (Learn).

Identity patterns

Minimal non-XML sketch (after edit)

12.0
4
1 SQLCHAR 0 50  ","  1 Sku       ""
2 SQLCHAR 0 20  ","  2 Qty       ""
3 SQLCHAR 0 30  ","  3 Location  ""
4 SQLCHAR 0 24  "\r\n" 4 UpdatedUtc ""

(Version line / column count / field defs — edit terminators and server ordinals to match your table. Generate first; don’t hand-author from scratch.)

7. Scripting workshop (PS → staging → proc → Blazor)

Pattern: file → staging (bcp) → stored proc → app views/procs → Blazor.

CLI-07 owns the full pipeline contract. This section is the bcp hop plus the sqlcmd verify/EXEC handoff (CLI-01).

7.1 One-shot load

# Secrets: Integrated (-T) or SecretManagement — never commit -P
$Server   = 'sql01'
$Db       = 'AppDb'
$Data     = 'D:\drops\inventory_20260912.csv'
$Fmt      = 'D:\deploy\fmt\InventoryStaging.fmt'
$stamp    = Get-Date -Format 'yyyyMMdd_HHmmss'
$Err      = "D:\logs\bcp_inv_$stamp.err"
$OutLog   = "D:\logs\bcp_inv_$stamp.out"

bcp "dbo.InventoryStaging" in $Data `
  -S $Server -d $Db -T -f $Fmt -F 2 -b 50000 -m 10 `
  -e $Err -o $OutLog -a 65535 `
  -h "TABLOCK, CHECK_CONSTRAINTS"

if ($LASTEXITCODE -ne 0) {
    throw "bcp in failed exit=$LASTEXITCODE; see $Err / $OutLog"
}

# Verify + transform via sqlcmd (SQL-CLI-01)
sqlcmd -S $Server -d $Db -E -b -V 11 -Q `
  "SET NOCOUNT ON; EXEC dbo.usp_Inventory_LoadFromStaging; SELECT COUNT(*) AS Staged FROM dbo.InventoryStaging;"
if ($LASTEXITCODE -ne 0) { throw "usp_Inventory_LoadFromStaging failed" }

# Blazor Server reads dbo.vw_Inventory_Current / EXEC dbo.usp_Inventory_GetPage — not the flat file

7.2 Loop files / servers

$Fmt     = 'D:\deploy\fmt\InventoryStaging.fmt'
$servers = @('sql01','sql02')

Get-ChildItem D:\drops\*.csv | ForEach-Object {
    foreach ($s in $servers) {
        $err = "D:\logs\$($_.BaseName)_$s.err"
        Write-Host "=== $($_.Name) → $s ===" -ForegroundColor Cyan
        bcp dbo.InventoryStaging in $_.FullName `
          -S $s -d AppDb -T -f $Fmt -F 2 -b 50000 -e $err
        if ($LASTEXITCODE -ne 0) {
            Write-Error "Fail $($_.Name) on $s exit=$LASTEXITCODE; see $err"
            continue
        }
        sqlcmd -S $s -d AppDb -E -b -V 11 -Q "SET NOCOUNT ON; EXEC dbo.usp_Inventory_LoadFromStaging;"
        if ($LASTEXITCODE -ne 0) { Write-Error "proc failed on $s"; continue }
    }
}

7.3 Wrapper

function Invoke-Bcp {
    param([Parameter(ValueFromRemainingArguments)]$BcpArgs)
    & bcp @BcpArgs
    if ($LASTEXITCODE -ne 0) {
        throw "bcp failed exit=$LASTEXITCODE args=$($BcpArgs -join ' ')"
    }
}

Invoke-Bcp dbo.Inventory out D:\export\Inventory.bcp -S sql01 -d AppDb -T -n

7.4 Hygiene

7.5 When SqlBulkCopy / Write-SqlTableData wins

Already holding rows in PowerShell / .NET? Prefer CLI-03 — no temp file, typed mapping, same staging→proc pattern. Use bcp when the artifact is a file (vendor drop, inventory extract on disk).

8. Gotchas

9. Recipes (paste and run)

Placeholders: sql01 / AppDb / contoso.database.windows.net. Swap in yours. Every recipe checks $LASTEXITCODE after native exes.

1) Export table → CSV (Unicode)

bcp "AppDb.dbo.Inventory" out "D:\export\Inventory.csv" -S sql01 -T -w -t, -r\n
if ($LASTEXITCODE -ne 0) { throw "bcp out failed: $LASTEXITCODE" }

2) queryout filtered extract

bcp "SELECT Sku, Qty, UpdatedUtc FROM AppDb.dbo.Inventory WHERE UpdatedUtc >= '2026-09-01'" `
  queryout "D:\export\Inventory_delta.csv" -S sql01 -d AppDb -T -w -t,
if ($LASTEXITCODE -ne 0) { throw "queryout failed: $LASTEXITCODE" }

3) Generate format file for CSV import

bcp AppDb.dbo.InventoryStaging format nul -c -t, -f D:\deploy\fmt\InventoryStaging.fmt -S sql01 -T
if ($LASTEXITCODE -ne 0) { throw "format failed: $LASTEXITCODE" }
# Edit .fmt: skip identity (server column ordinal 0), confirm \r\n on last field

4) Import CSV with format file (skip header)

bcp dbo.InventoryStaging in "D:\drops\vendor.csv" `
  -S sql01 -d AppDb -T -f D:\deploy\fmt\InventoryStaging.fmt `
  -F 2 -b 50000 -m 10 -e D:\logs\vendor.err -h "TABLOCK"
if ($LASTEXITCODE -ne 0) { throw "bcp in failed: $LASTEXITCODE" }

5) Native round-trip (SQL → file → SQL)

bcp AppDb.dbo.Inventory out D:\backup\Inventory.bcp -S sql01 -T -n -a 65535
if ($LASTEXITCODE -ne 0) { throw "native out failed" }
bcp AppDb.dbo.Inventory_Clone in D:\backup\Inventory.bcp -S sql01 -T -n -b 100000 -h "TABLOCK"
if ($LASTEXITCODE -ne 0) { throw "native in failed" }

6) UTF-8 vendor file (-C65001)

# After generating .fmt, strip collation names from character columns so 65001 wins
bcp dbo.InventoryStaging in D:\drops\vendor_utf8.csv `
  -S sql01 -d AppDb -T -c -C 65001 -t, -F 2 -f D:\deploy\fmt\InventoryStaging_utf8.fmt `
  -b 50000 -e D:\logs\vendor_utf8.err
if ($LASTEXITCODE -ne 0) { throw "UTF-8 import failed: $LASTEXITCODE" }

7) PowerShell wrapper with $LASTEXITCODE

function Invoke-Bcp {
    param([Parameter(ValueFromRemainingArguments)]$BcpArgs)
    & bcp @BcpArgs
    if ($LASTEXITCODE -ne 0) {
        throw "bcp failed exit=$LASTEXITCODE args=$($BcpArgs -join ' ')"
    }
}

Invoke-Bcp dbo.Inventory out D:\export\Inventory.bcp -S sql01 -d AppDb -T -n

8) Load staging → EXEC proc → verify (feeds Blazor)

$S='sql01'; $D='AppDb'
bcp dbo.InventoryStaging in D:\drops\inv.csv -S $S -d $D -T `
  -f D:\deploy\fmt\InventoryStaging.fmt -F 2 -b 50000 -e D:\logs\inv.err
if ($LASTEXITCODE -ne 0) { throw "bcp in failed" }

sqlcmd -S $S -d $D -E -b -V 11 -Q "SET NOCOUNT ON; EXEC dbo.usp_Inventory_LoadFromStaging;"
if ($LASTEXITCODE -ne 0) { throw "proc failed" }

# Blazor uses: EXEC dbo.usp_Inventory_GetPage @Skip=@p0, @Take=@p1
sqlcmd -S $S -d $D -E -b -Q "SET NOCOUNT ON; SELECT TOP 5 * FROM dbo.vw_Inventory_Current;"
if ($LASTEXITCODE -ne 0) { throw "verify failed" }

9) Keep identities + fire triggers (rare; intentional)

bcp dbo.Inventory in D:\restore\Inventory.bcp -S sql01 -d AppDb -T -n -E `
  -h "FIRE_TRIGGERS, CHECK_CONSTRAINTS" -b 10000 -e D:\logs\id_restore.err
if ($LASTEXITCODE -ne 0) { throw "identity restore failed: $LASTEXITCODE" }

10) Azure SQL Entra interactive export

bcp dbo.Inventory out D:\export\Inventory_az.csv `
  -S contoso.database.windows.net -d AppDb -G -U 'rick@contoso.com' `
  -w -t, -l 60
if ($LASTEXITCODE -ne 0) { throw "Azure bcp out failed: $LASTEXITCODE" }

11) Lab self-signed cert (bcp 18+)

bcp dbo.InventoryStaging in D:\drops\inv.csv `
  -S "tcp:sql01.contoso.local,1433" -d AppDb -T `
  -f D:\deploy\fmt\InventoryStaging.fmt -F 2 `
  -Y m -u -b 50000 -e D:\logs\lab.err
if ($LASTEXITCODE -ne 0) { throw "lab bcp in failed: $LASTEXITCODE" }

12) Row window / sample slice (-F / -L)

# Import only rows 2–10001 (skip header, first 10k data rows) for a staging dry-run
bcp dbo.InventoryStaging in D:\drops\big_vendor.csv `
  -S sql01 -d AppDb -T -f D:\deploy\fmt\InventoryStaging.fmt `
  -F 2 -L 10001 -b 5000 -e D:\logs\sample.err -h "TABLOCK"
if ($LASTEXITCODE -ne 0) { throw "sample import failed: $LASTEXITCODE" }

Quick decision card

File on client / Agent, need speed?          → bcp
File on SQL host, T-SQL job?                 → BULK INSERT
In-process .NET / Blazor worker?             → SqlBulkCopy / Write-SqlTableData (CLI-03)
Verify / EXEC proc / row samples?            → sqlcmd (CLI-01)
SQL↔SQL internal hop?                        → bcp -n / -N
Vendor CSV / Excel-friendly?                 → bcp -w or -c -t, + format file
Azure SQL?                                   → bcp -G (not -T); watch firewall/DTU
Need Encrypt/TSC / Kerberos matrix?          → CLI-06
Need full Collect→Stage→Load→Blazor?         → CLI-07

What’s next

Need verify / EXEC after load?             → sqlcmd -b + $LASTEXITCODE   (CLI-01)
Need in-process bulk without a file?       → Write-SqlTableData / SqlBulkCopy (CLI-03)
Need hop / Encrypt / identity matrix?      → CLI-06
Need the pipeline, not the client?         → CLI-07

Sources (verify periodically):
bcp utility · Download/install bcp · Entra auth in bcp · Create a format file · BULK INSERT · Use a format file to skip a table column