SQL Server BCP使用小結

用法: bcp {dbtable | query} {in | out | queryout | format} 資料檔案
  [-m 最大錯誤數]             [-f 格式檔案]         [-e 錯誤檔案]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本機類型]               [-c 字元類型]         [-w 寬字元類型]
  [-N 將非文本保持為本機類型] [-V 檔案格式版本]     [-q 引號識別項]
  [-C 字碼頁說明符]           [-t 欄位結束字元]       [-r 行終止符]
  [-i 輸入檔案]               [-o 輸出檔案]         [-a 資料包大小]
  [-S 伺服器名稱]             [-U 使用者名稱]           [-P 密碼]
  [-T 可信串連]               [-v 版本]             [-R 允許使用地區設定]
  [-k 保留空值]               [-E 保留標識值]
  [-h"載入提示"]              [-x 產生xml 格式檔案]

--不利用格式檔案的匯入匯出(以下樣本皆省略了-S參數，因為是在本機，又是預設執行個體)
--簡單匯出表
exec master..xp_cmdshell 'bcp db_oa.dbo.T_User_list  out c:\a.xls -c -T'    --"-T"信任連接
exec master..xp_cmdshell 'bcp db_oa.dbo.T_User_list  out c:\a.txt -c -Usa -Pxxxxx'
--過濾匯出表
exec master..xp_cmdshell 'bcp "select top 3 * from db_oa.dbo.t_user_list"  queryout c:\a.txt -c -T'
--簡單匯入表
exec master..xp_cmdshell 'bcp db_oa.dbo.T_User_list_2  in c:\a.txt -c -T'
exec master..xp_cmdshell 'bcp db_oa.dbo.T_User_list_2  in c:\a.txt -c -T -E'    --加上"-E"啟用identity_insert on

--下面是利用XML格式檔案進行資料的匯入！
--建立測試表
USE db_oa;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

--輸出XML格式檔案
--說明一下：-t","是指定欄位分隔符號，稍後我們會講到
exec master..xp_cmdshell 'bcp db_oa..MyTestFormatFiles format nul -c -t"," -x -f c:\myTestFormatFiles.Xml -T'

/*
格式化後的檔案內容如下：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

這裡我們對檔案關鍵字稍作解釋：
    <RECORD>父節點中的內容是對源檔案的格式化定義，即你要匯入的資料檔案
        <FIELD ID="1"(這裡是來源資料欄位ID) xsi:type="CharTerm"(欄位類型) TERMINATOR=","(欄位分隔符號) MAX_LENGTH="7"(最大長度)/>
            不難看出，我們最常修改的就是TERMINATOR，可以修改為"|"等你需要的分隔字元,注意最後一個欄位的分隔字元為"\r\n"
    </RECORD>
    <ROW>該父節點的內容是目標資料的格式化定義，即你需要匯入資料檔案的地方
        <COLUMN SOURCE="1"(這個非常重要，指定的是上面來源資料的欄位ID，當來源資料和目標資料欄位順序或數目不一致時，我們可以修改這裡達到我們想要的結果) NAME="Col1"(目標欄位名稱) xsi:type="SQLSMALLINT"(目標欄位類型)/>
    </ROW>
*/
--下面我們建立來源資料檔案，將以下內容粘貼到我們指定的資料檔案C:\myTestFormatFiles-c.txt中
/*
    10,Field2,Field3,Field4
    15,Field2,Field3,Field4
    46,Field2,Field3,Field4
    58,Field2,Field3,Field4
*/
--利用bcp格式檔案匯入資料
exec master..xp_cmdshell 'bcp db_oa..myTestFormatFiles in C:\myTestFormatFiles-c.txt -f C:\myTestFormatFiles.Xml -T'
--查看一下匯入的資料
select * from myTestFormatFiles
/*
    Col1    Col2    Col3    Col4
    10    Field2    Field3    Field4
    15    Field2    Field3    Field4
    46    Field2    Field3    Field4
    58    Field2    Field3    Field4
*/

--使用BULK INSERT匯入資料
USE db_oa;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.txt' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Xml');
GO
SELECT * FROM myTestFormatFiles;
GO
/*
    Col1    Col2    Col3    Col4
    10    Field2    Field3    Field4
    15    Field2    Field3    Field4
    46    Field2    Field3    Field4
    58    Field2    Field3    Field4
*/

--使用OPENROWSET 大容量行集提供者匯入
USE db_oa;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.txt',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO
/*
    Col1    Col2    Col3    Col4
    10    Field2    Field3    Field4
    15    Field2    Field3    Field4
    46    Field2    Field3    Field4
    58    Field2    Field3    Field4
*/

--刪除測試表
DROP TABLE myTestFormatFiles