BCP 資料的匯入和匯出

BCP 命令的引數很多，使用 -h 檢視幫助資訊，注意：引數是區分大小寫的

使用BCP命令匯出和匯入資料常用的引數如下

bcp {[[database_name.][schema_name]].{table_name | view_name} | "query"}

{in | out | queryout}  資料檔案

[-c 字元型別]  | [-w 寬字元型別]
[-t 欄位終止符]    [-r 行終止符]
[-i 輸入檔案]       [-o 輸出檔案]        
[-S 伺服器名稱]   [-U 使用者名稱]           [-P 密碼]
[-T 可信連線]      [-d 資料庫名稱]
[-k 保留NULL值]

-c 使用char型別做為儲存型別，沒有字首且以"\t"做為欄位分割符，以"\n"做為行分割符。

-w 使用Unicode字符集拷貝資料，在資料庫中，需要將Table Column設定為 nchar或nvarchar儲存型別。如果 -c 和 -w 同時指定，那麼 -w 將覆蓋 -c。

-t field_term 指定column分割符，預設是"\t"。

-r row_term 指定row分割符，預設是"\n"。

-S server_name[ \instance_name] 指定要連線的SQL Server伺服器的例項，如果未指定此選項，BCP連線本機的SQL Server預設例項。如果要連線某臺機器上的預設例項，只需要指定機器名即可。

-U login_id 指定連線SQL Sever的使用者名稱。

-P password 指定連線SQL Server的使用者名稱密碼。

-T 指定BCP使用信任連線登入SQL Server。如果未指定-T，必須指定-U和-P。

-d 指定資料庫名稱

-k 指定空列使用null值插入，而不是這列的預設值。

 

一，使用bcp 將整個table中的資料匯出到txt或csv文件中

bcp db_study.dbo.sales out D:\test.txt S . U sa P sa t  S . U sa P sa t  w

二，使用 query statement 將查詢結果匯出到txt 或 csv文件中

1，配置SQL Server，允許執行 xp_cmdshell 命令

-- 允許配置高階選項  EXEC master.sys.sp_configure 'show advanced options', 1  -- 重新配置  RECONFIGURE  -- 啟用xp_cmdshell  EXEC master.sys.sp_configure 'xp_cmdshell', 1  --重新配置  RECONFIGURE

否則，SQL Server 會丟擲錯誤資訊

SQL Server 阻止了對元件“xp_cmdshell”的 過程“sys.xp_cmdshell”的訪問，因為此元件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用“xp_cmdshell”。有關啟用“xp_cmdshell”的詳細資訊，請搜尋 SQL Server 聯機叢書中的“xp_cmdshell”。

啟用“xp_cmdshell” 被認為是不安全的，在使用完 “xp_cmdshell” 命令之後，使用以下script將其禁用。 

-- 允許配置高階選項  EXEC master.sys.sp_configure 'show advanced options', 1  -- 重新配置  RECONFIGURE  -- 禁用xp_cmdshell  EXEC master.sys.sp_configure 'xp_cmdshell', 0--重新配置  RECONFIGURE

2，使用  xp_cmdshell 命令執行BCP命令，將資料匯出

EXEC master..xp_cmdshell 'bcp "SELECT [Store] ,[Item] ,[Color] ,[Quantity] FROM [db_study].[dbo].[Inventory]" queryout D:\test.txt -S . -U sa -P sa -t "\t" -w 'EXEC master..xp_cmdshell 'bcp "SELECT [Store] ,[Item] ,[Color] ,[Quantity] FROM [db_study].[dbo].[Inventory]" queryout D:\test.csv -S . -U sa -P sa -t "," -w '

3，使用  xp_cmdshell 命令，也可以將整個Table的資料匯出

EXEC master..xp_cmdshell 'bcp [db_study].[dbo].[Inventory] out D:\test.txt -S . -U sa -P sa -t "\t" -w 'EXEC master..xp_cmdshell 'bcp [db_study].[dbo].[Inventory] out D:\test.csv -S . -U sa -P sa -t "," -w '

三，將資料匯入到SQL Server

CREATE TABLE [dbo].[Inventory_LoadIn](    [Store] [nvarchar](2) NULL,    [Item] [varchar](20) NULL,    [Color] [varchar](10) NULL,    [Quantity] [int] NULL)

1，使用BCP，將txt文件中的資料匯入到table [dbo].[Inventory_LoadIn] 中

bcp [db_study].[dbo].[Inventory_LoadIn] in D:\test.txt -S . -U sa -P sa -t "\t" -w 

bcp [db_study].[dbo].[Inventory_LoadIn] in D:\test.csv -S . -U sa -P sa -t "," -w

2，使用xp_cmdshell 命令執行bcp，將資料匯入到資料庫table中

EXEC master..xp_cmdshell 'bcp [db_study].[dbo].[Inventory_LoadIn] in D:\test.txt -S . -U sa -P sa -t "\t" -w 'EXEC master..xp_cmdshell 'bcp [db_study].[dbo].[Inventory_LoadIn] in D:\test.csv -S . -U sa -P sa -t "," -w '