`bcp` (Bulk Copy Program) 是 SQL Server 原生高效能大量資料移動工具,配合 `xp_cmdshell` 可在 T-SQL 內直接調用,實現排程自動化備份/匯出。本文提供生產級存儲過程範本,涵蓋權限開啟、動態腳本生成、編碼處理、路徑管理等完整細節。
-- 開啟進階選項與 xp_cmdshell
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
安全警示:`xp_cmdshell` 擁有作業系統級權限,生產環境建議:
① 僅授予 `sysadmin` 執行權限
② 使用 Proxy Account (`##xp_cmdshell_proxy_account##`) 限制 OS 權限
③ 完成作業後立即關閉 `xp_cmdshell`
CREATE PROCEDURE dbo.usp_ExportTablesByPattern
@FilePath NVARCHAR(260) = N'D:\BCP_Export\', -- 必須為 SQL Server 服務帳號可寫入路徑
@DBName SYSNAME = N'DHL_POSM_WS',
@SchemaPattern NVARCHAR(100) = N'dbo',
@TablePattern NVARCHAR(100) = N'WM_ORDER%,WM_PICKING%,RP_%', -- 逗號分隔 LIKE 模式
@DateFilter NVARCHAR(200) = N'DATEDIFF(MONTH, MODIFY_TIME, GETDATE()) > 10', -- 增量條件
@ServerInstance NVARCHAR(100) = N'127.0.0.1\SQLEXPRESS',
@LoginUser SYSNAME = N'DHL_POSM_WS',
@LoginPwd NVARCHAR(128) = N'DHLposm',
@UseUnicode BIT = 1 -- 1=-w (Unicode), 0=-c (字元)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX), @BCP NVARCHAR(MAX), @TableName SYSNAME, @FileName NVARCHAR(260);
DECLARE @Patterns TABLE (Pattern NVARCHAR(100));
-- 解析逗號分隔模式
INSERT INTO @Patterns
SELECT TRIM(value) FROM STRING_SPLIT(@TablePattern, ',');
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.tables t
WHERE EXISTS (SELECT 1 FROM @Patterns p WHERE t.name LIKE p.Pattern);
OPEN cur;
FETCH NEXT FROM cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = @FilePath + @TableName + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.xls';
SET @SQL = N'SELECT * FROM ' + @DBName + N'..' + @TableName + N' WHERE ' + @DateFilter;
SET @BCP = N'bcp "' + @SQL + N'" queryout "' + @FileName + N'" '
+ CASE @UseUnicode WHEN 1 THEN N'-w' ELSE N'-c' END
+ N' -S"' + @ServerInstance + N'" -U"' + @LoginUser + N'" -P"' + @LoginPwd + N'"';
PRINT @BCP; -- 記錄便於除錯
EXEC master..xp_cmdshell @BCP;
FETCH NEXT FROM cur INTO @TableName;
END
CLOSE cur; DEALLOCATE cur;
END
| 參數 | 用途 | 常見值 |
|---|---|---|
| `-c` | 字元模式,欄位 `\t` 分隔,列 `\n` 分隔 | ASCII 資料、跨平台匯入 |
| `-w` | Unicode 模式,欄位 `\t`,列 `\n`,雙位元組字元 | 含中文/多語系,**解決亂碼首選** |
| `-S` | 目標實例名稱 | `127.0.0.1\SQLEXPRESS` 或 `(local)` |
| `-U`/`-P` | 登入帳密 | 建議使用 Windows 驗證 `-T` 避免明文密碼 |
| `-q` | 啟用引號識別碼 (支援特殊字元表名) | 搭配 `QUOTENAME` 使用 |
| `-k` | 保留 NULL 值 (不轉空字串) | 維持資料語意 |
將上述存儲過程部署至維護資料庫,配合 SQL Agent Job 每日執行,即可構建零成本、高效能的資料歸檔/異地備份管線。