SQL Server bcp 命令详解与高效数据导入导出实战

• 导入数据 (in)：将外部文件的数据导入到数据库表中。
• 导出数据 (out)：将数据库表或视图的整表数据导出到外部文件。
• 查询导出 (queryout)：基于指定的 SELECT SQL 语句结果导出数据，而非整表。
• 生成格式文件 (format)：创建描述表结构的格式文件，供后续导入/导出使用。

• -f format_file：指定已存在的格式文件路径；若配合 format 动作，则指定要生成的格式文件名。
• -x：与 -f 配合，生成 XML 格式的格式文件，便于阅读和编辑。
• -F first_row / -L last_row：指定从第几行开始操作到第几行结束。默认首行为1。
• -c：使用字符类型（char）进行读写，字段分隔符默认为制表符(\t)，行分隔符为换行符(\n)。
• -w：使用 Unicode 字符类型（nchar）进行读写，适用于包含特殊字符或非 ASCII 编码的数据。
• -t field_term：自定义字段分隔符，如 , (CSV格式)。
• -r row_term：自定义行分隔符，如 \n。
• -S server_name[\instance_name]：指定连接的服务器实例，省略则连接本地默认实例。
• -U login_id / -P password：指定 SQL Server 身份验证的用户名和密码。
• -T：使用信任连接（Windows 身份验证），无需提供密码。
• -k：导入时，若源文件中某列值为空，则在数据库中插入 NULL，而不是该列的默认值。

如何导出数据库数据

1. 第一步：导出完整表或视图结构

   目的：快速获取某张表的所有数据备份。可以使用用户名密码连接，或使用 Windows 信任连接。

   入口：命令行或 SQL Server Management Studio (SSMS) 查询窗口。

   动作：使用 out 关键字指定输出文件路径，并附加数据类型参数 -c。

   -- 示例：导出 AdventureWorks 库中的 sales.currency 表，使用密码登录
   EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"your_password"'
   
   -- 示例：使用信任连接（推荐生产环境配置好权限后使用）
   EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency out c:\currency2.txt -c -T'

   结果：成功后将返回 "rows copied" 统计信息，并在指定路径生成文本文件，字段间以制表符分隔。

2. 第二步：基于 SQL 查询过滤导出

   目的：仅导出满足特定条件的部分数据，避免全表扫描带来的资源浪费。

   入口：使用 queryout 动作，需在双引号内包裹完整的 SELECT 语句。

   -- 导出查询结果的前20条记录
   EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:\filtered_result.txt -c -T'

   进阶限制：可通过 -F 和 -L 参数精确控制从查询结果的第几行取到哪一行。

   -- 从查询结果的第10行取到第13行
   EXEC master..xp_cmdshell 'bcp "SELECT TOP 50 * FROM AdventureWorks.sales.currency" queryout c:\subset.txt -c -F 10 -L 13 -T'

格式文件的生成与高级应用

1. 第三步：生成标准格式文件 (.fmt)

   目的：自动获取当前表的元数据定义，便于调试或手动修改分隔符。

   EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:\currency_fmt.fmt -c -T'

   生成的 .fmt 文件是纯文本，第一行为版本号和列数，后续每行对应一列的定义。例如：
   9.0
3
1 SQLCHAR 0 6 "\t" 1 CurrencyCode ...

2. 第四步：生成 XML 格式文件

   目的：当需要直观查看或复杂调整列映射关系时使用 XML 格式，可读性更强。

   EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:\currency_xml.xml -x -c -T'

如何导入数据到数据库

1. 第五步：利用格式文件执行精准导入

   目的：确保导入数据的严格校验，忽略不符合格式的记录或处理特定分隔符（如CSV）。

   准备：假设有一个包含逗号分隔符的 CSV 文件需要导入名为 currency1 的目标表。

   动作：使用 in 动作，并通过 -f 加载之前生成的格式文件，或通过 -t 指定逗号分隔符。

   -- 使用标准的格式文件导入
   EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:\currency1.txt -c -f c:\currency_fmt.fmt -T'
   
   -- 或者针对 CSV 文件格式简化命令 (不依赖预先生成的格式文件)
   EXEC master..xp_cmdshell 'bcp AdventureWorks.dbo.table_name in E:\export.csv -c -t"," -r"\n" -T'

   注意：如果使用的是 XML 格式文件，必须额外添加 -x 参数告知 bcp 格式文件类型。

前置条件与排错

-- 启用高级配置选项
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- 启用 xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO