數(shù)據(jù)導入導出工具BCP詳解

1、數(shù)據(jù)導入導出工具BCP詳解IT168技術(shù)文檔】bcp是SQL Server中負責導入導出數(shù)據(jù)的一個命令行工具，它是基于 DB-Library的，并且能以并行的方式高效地導入導出大批量的數(shù)據(jù)。bcp可以將數(shù)據(jù)庫的表或 視圖直接導出，也能通過SELECT FROM語句對表或視圖進行過濾后導出。在導入導出數(shù)據(jù)時， 可以使用默認值或是使用一個格式文件將文件中的數(shù)據(jù)導入到數(shù)據(jù)庫或?qū)?shù)據(jù)庫中的數(shù)據(jù)導出 到文件中。下面將詳細討論如何利用bcp導入導出數(shù)據(jù)。1. bcp的主要參數(shù)介紹bcp共有四個動作可以選擇。導入。這個動作使用in命令完成，后面跟需要導入的文件名。導出。這個動作使用out命令完成，后面跟需要

2、導出的文件名。使用SQL語句導出。這個動作使用queryout命令完成，它跟out類似，只是數(shù)據(jù)源不是表或視圖名，而是SQL 語句。導出格式文件。這個動作使用format命令完成，后而跟格式文件名。下面介紹一些常用的選項：-f format_fileformat_file表示格式文件名。這個選項依賴于上述的動作，如果使用的是 in或out， format_file表示已經(jīng)存在的格式文件，如果使用的是format則表示是要生成的格式文件。-x這個選項要和-f format_file配合使用，以便生成xml格式的格式文件。-F first_row指定從被導出表的哪一行導出，或從被導入文件的哪一行導

3、入。-L last_row指定被導出表要導到哪一行結(jié)束，或從被導入文件導數(shù)據(jù)時，導到哪一行結(jié)束。-c使用char類型做為存儲類型，沒有前綴且以t做為字段分割符，以n”做為行分割符。-w和-c類似，只是當使用Unicode字符集拷貝數(shù)據(jù)時使用，且以nchar做為存儲類型。-t field_term指定字符分割符，默認是t”。-r row_term指定行分割符，默認是n”。-S server_name instance_name指定要連接的SQL Server服務器的實例，如果未指定此選項，bcp連接本機的SQL Server 默認實例。如果要連接某臺機器上的默認實例，只需要指定機器名即可。-U

4、login_id指定連接SQL Sever的用戶名。-P password指定連接SQL Server的用戶名密碼。-T指定bcp使用信任連接登錄SQL Server。如果未指定-T,必須指定-U和-P。-k指定空列使用null值插入，而不是這列的默認值。2.如何使用bcp導出數(shù)據(jù)(1)使用bcp導出整個表或視圖。bcp AdventureWorks.sales.currency out c:currency1.tx-c-Usa-Ppassword-使用密碼連接或bcp AdventureWorks.sales.currency out c:currency1.tx-c-T-使用信任連接下面是

5、上述命令執(zhí)行后的輸出結(jié)果Starting copy.105 rows copied. Network packet size (bytes):4096Clock Time (ms.) Total 10 Average : (10500.00 rows per sec.)F面是currency1.txt的部分內(nèi)容AED EmiratiDirham1998-06-0100:00:00.000AFA Afghani1998-06-0100:00:00.000 ZWD Zimbabwe Dollar1998-06-0100:00:00.000在使用密碼登錄時需要將-U后的用戶名和-P后的密碼加上雙引號

6、。注：bcp除了可以在控制臺執(zhí)行外，還可以通過調(diào)用SQL Server的一個系統(tǒng)存儲過程 xp_cmdshell以SQL語句的方式運行bcp。如上述第一條命令可改寫為EXEC master.xp_cmdshellbcp AdventureWorks.sales.currency out c:currency1.txt -c -Usa -Ppassword執(zhí)行xp_cmdshell后，返回信息以表的形式輸出。為了可以方便地在SQL中執(zhí)行bcp,下 面的命令都使用xp_cmdshell執(zhí)行bcp命令。(2)對要導出的表進行過濾。bcp不僅可以接受表名或視圖名做為參數(shù)，也可以接受SQL做為參數(shù)。通過

7、SQL語句可以對要導出的表進行過濾，然后導出過濾后的記錄。EXEC master.xp_cmdshellbcp SELECT TOP 20 * FROM AdventureWorks.sales.currency queryout c:currency2.txt -c -Usa -Ppasswordbcp還可以通過簡單地設置選項對導出的行進行限制。EXEC master.xp_cmdshellbcp SELECT TOP 20 * FROM AdventureWorks.sales.currency queryout c:currency2.txt -F 10 -L 13 -c -Usa -P

8、password這條命令使用了兩個參數(shù)-F 10和-L 13，表示從SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出來的結(jié)果中取第10條到13條記錄進行導出。3.如何使用bcp導出格式文件bcp不僅可以根據(jù)表、視圖導入導出數(shù)據(jù)，還可以配合格式文件對導入導出數(shù)據(jù)進行限制。 格式文件以純文本文件形式存在，分為一般格式和xml格式。用戶可以手工編寫格式文件，也 可以通過bcp命令根據(jù)表、視圖自動生成格式文件。EXECmaster.xp_cmdshellbcpAdventureWorks.sales.currency format nul -f

9、c:currency_format1.fmt -c -T上述命令將currency表的結(jié)構(gòu)生成了一個格式文件currency_format1.fmt，下面是這個格式 文件的內(nèi)容。9.031 SQLCHAR0 6 t 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS2 SQLCHAR0 100 t2 Name SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR0 24 rn3 ModifiedDate這個格式文件記錄了這個表的字段（共3個字段）類型、長度、字符和行分割符和字段名等 信息。bcp還可以通過-x選項生成xml格式的格式文

10、件。EXECmaster.xp_cmdshellbcpAdventureWorks.sales.currency format nul -fc:currency_format2.fmt -x -c -Txml格式文件所描述的內(nèi)容和普通格式文件所描述的內(nèi)容完全一樣，只是格式不同。4.如何使用bcp導入數(shù)據(jù)bcp可以通過in命令將上面所導出的currencyl.txt和currency2.txt再重新導入到數(shù)據(jù)庫中， 由于currency有主鍵，因此我們將復制一個和currency的結(jié)構(gòu)完全一樣的表。SELECT TOP 0* INTO AdventureWorks.sales.currencyl

11、FROMAdventureWorks.sales.currency將數(shù)據(jù)導入到currency1表中EXEC master.xp_cmdshellbcp AdventureWorks.sales.currency1 in c:currency1.txt -c -T導入數(shù)據(jù)也同樣可以使用-F和-L選項來選擇導入數(shù)據(jù)的記錄行。EXEC master.xp_cmdshellbcp AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L13 -T在導入數(shù)據(jù)時可以根據(jù)已經(jīng)存在的格式文件將滿足條件的記錄導入到數(shù)據(jù)庫中，不滿足則 不導入。如

12、上述的格式文件中的第三個字段的字符長度是24，如果某個文本文件中的相應字段 的長度超過24，則這條記錄將不被導入到數(shù)據(jù)庫中，其它滿足條件的記錄正常導入。使用普通的格式文件EXEC master.xp_cmdshellbcp AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T使用xml格式的格式文件EXEC master.xp_cmdshellbcp AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c