SQL Server BCP使用小結 轉

標籤：http   io   ar   os   使用   for   檔案   資料   on   

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
用法: 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
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
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
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
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
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
Field2 Field3 Field4
*/

--刪除測試表
DROP TABLE myTestFormatFiles

SQL Server BCP使用小結 轉