BCP和Bulk into 命令使用（待整理更新）

標籤：blog   color   使用   檔案   資料   os   

BCP和Bulk into 命令使用（待整理更新）

BCP和Bulk into 命令使用（待整理更新）

 

bcp命令是SQL2005 一個實用的資料匯出匯入工具，我們可以運用這個命令方便的到處匯入資料

 

首先我們欣賞一下bcp命令的 格式：

 

BCP是SQL Server中負責匯入匯出資料的一個命令列工具，它是基於DB-Library的，並且能以並行的方式高效地匯入匯出大批量的資料 BCP除了可以在控制台執行外，還可以通過調用SQL Server的一個系統預存程序xp_cmdshell以SQL語句的方式運行BCP。如：EXEC master..xp_cmdshell ‘BCP NTS.dbo.T_User out c:\User.txt -c -U"sa" -P"password"‘ 1. 四個動作 01. 匯入：這個動作使用IN命令完成，後面跟需要匯入的檔案名稱 02. 匯出：這個動作使用OUT命令完成，後面跟需要匯出的檔案名稱，資料來源是表或者視圖 03. 使用SQL語句匯出：這個動作使用QueryOut命令完成，跟OUT類似，資料來源是SQL語句 04. 匯出格式檔案：這個動作使用Format命令完成，後而跟格式檔案名稱 2. 常用的選項 01. -f Format_File Format_File表示格式檔案名稱。這個選項依賴於上述的動作，如果使用的是IN或OUT，Format_File表示已經存在的格式檔案，如果使用的是Format則表示是要產生的格式檔案 02. -x 這個選項要和-f Format_File配合使用，以便產生XML格式的格式檔案 03. -F First_Row 指定從被匯出表的哪一行匯出，或從被匯入檔案的哪一行匯入 04. -L last_Row 指定被匯出表要導到哪一行結束，或從被匯入檔案導資料時，導到哪一行結束 05. -c 使用Char類型做為儲存類型，沒有首碼且以"\t"做為欄位分割符，以"\n"做為行分割符 06. -w 和-c類似，只是當使用Unicode字元集拷貝資料時使用，且以nChar做為儲存類型 07. -t Field_Term 指定字元分割符，預設是"\t" 08. -r Row_Term 指定行分割符，預設是"\n" 09. -S Server_Name[ \Instance_Name] 指定要串連的SQL Server伺服器的執行個體，如果未指定此選項，BCP串連原生SQL Server預設執行個體。如果要串連某台機器上的預設執行個體，只需要指定機器名即可 10. -U Login_ID 指定串連SQL Sever的使用者名稱 11. -P Password 指定串連SQL Server的使用者名稱密碼 12. -T 指定BCP使用信任連接登入SQL Server。如果未指定-T，必須指定-U和-P 13. -k 指定空列使用NULL值插入，而不是這列的預設值 3. 資料匯出 01. 匯出整個表或視圖 BCP NTS.dbo.T_User out c:\User.txt -c -U"sa" -P"password" --使用密碼串連（使用者名稱和密碼需要加上雙引號） BCP NTS.dbo.T_User out c:\User.txt -c -T --使用信任連接 02. 匯出SQL語句的目標資料 BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:\User.txt -c -U"sa" -P"password" BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:\User.txt -F 10 -L 13 -c -U"sa" -P"password" --匯出第10條到13條記錄 03. 匯出格式檔案 BCP NTS.dbo.T_User format nul -f c:\User_format1.fmt -c -T --把表結構資訊匯出到User_format1.fmt中 BCP NTS.dbo.T_User format nul -f c:\User_format2.fmt -x -c -T --把表結構資訊匯出XML中 04. 匯入資料 在匯入資料時可以根據已經存在的格式檔案將滿足條件的記錄匯入到資料庫中，不滿足則不匯入。如上述的格式檔案中的第三個欄位的字元長度是24，如果某個文字檔中的相應欄位的長度超過24，則這條記錄將不被匯入到資料庫中，其它滿足條件的記錄正常匯入 BCP NTS.dbo.T_User in c:\User1.txt -c -T BCP NTS.dbo.T_User in c:\User1.txt -c -F 10 -L 13 -T --匯入時也可使用-F和-L選項來選擇目標資料 BCP NTS.dbo.T_User in c:\User1.txt -F 10 -L 13 -c -f c:\User_format1.fmt -T --使用普通的格式檔案 BCP NTS.dbo.T_User in c:\User1.txt -F 10 -L 13 -c -x -f c:\User_format2.fmt -T --使用XML格式的格式檔案 05. 實際匯出的例子 EXEC master..xp_cmdshell ‘BCP SZC.dbo.T_User out c:\test1.csv -c -U"sa" -P"qwert1"‘ --使用密碼串連 EXEC master..xp_cmdshell ‘BCP SZC.dbo.T_User out c:\test1.csv -c -T‘ --使用信任連接 4. 其他方法匯入匯出資料 01. 匯入資料 SELECT * INTO authors1FROM OPENDATASOURCE(‘SQLOLEDB‘, ‘Data Source=192.1.1.2; UserID=sa; Password=qwert1‘).pubs.dbo.authors 02. 匯出資料 INSERT INTO OPENDATASOURCE(‘SQLOLEDB‘, ‘Data Source=192.1.1.2; UserID=sa; Password=qwert1‘).test.dbo.authorsSELECT * FROM pubs.dbo.authors INSERT INTO OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘,‘Text;DATABASE=c:\‘)[data#txt] SELECT * FROM text1 INSERT INTO OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0‘,‘Text;DATABASE=c:\‘, [data#txt]) SELECT * FROM text1 03. 匯入Excel資料 SELECT * INTO excel FROM OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘, ‘Excel 5.0;DATABASE=c:\book1.xls‘ )[Sheet1$] 04. 匯入到Excel INSERT INTO OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘, ‘Excel 5.0;DATABASE=c:\book1.xls‘ )[Sheet1$] SELECT * FROM excel

如果你看不懂  沒關係 我總結了一下 如何簡單的運用這個命令

SQL 2005 運用這個命令的時候 需要開啟  XP_CMDSHELL 這個預存程序 ，預設情況下SQL2005 這個預存程序是關閉的，我們用SQL語句把他開啟：

EXEC SP_CONFIGURE ‘show advanced options‘,1;reconfigureEXEC SP_CONFIGURE ‘xp_cmdshell‘,1;reconfigure