第07章 數(shù)據(jù)的導(dǎo)入和導(dǎo)出

1、第7章第7章 數(shù)據(jù)的導(dǎo)入和導(dǎo)出 大多應(yīng)用程序都設(shè)計成允許用戶操作單獨的數(shù)據(jù)。但實際應(yīng)用中經(jīng)常都需要導(dǎo)入或?qū)С龃罅繑?shù)據(jù)。 導(dǎo)入大量數(shù)據(jù)時，使用INSERT語句的執(zhí)行效率不是特別好。 導(dǎo)出數(shù)據(jù)時，若向應(yīng)用程序返回一個結(jié)果集，再由應(yīng)用程序?qū)⑿袑懭胛募蚱渌康牡兀室膊桓摺?SQL Server專門為這些操作提供了新的功能，允許高效地導(dǎo)入和導(dǎo)出大量數(shù)據(jù)，同時將資源的消耗降至最低。第第7章章 數(shù)據(jù)的導(dǎo)入和導(dǎo)出數(shù)據(jù)的導(dǎo)入和導(dǎo)出兩年前，我合作過的一個企業(yè)有一個完整的分公司專門負(fù)責(zé)為合作伙伴履行訂單。合作伙伴每天都將訂單文件上傳到FTP服務(wù)器。這些文件會被解析并加載到數(shù)據(jù)庫，然后通過一系列出貨、打包、發(fā)貨

2、和結(jié)帳過程進(jìn)行處理。遺憾的是，導(dǎo)入每個合作伙伴的訂單往往都要花上2-7個小時。但為了滿足和客戶的協(xié)議，要求所有合作伙伴的文件都必須在1小時內(nèi)導(dǎo)入完成。一個文件夾中往往會有30或更多的文件等待處理。除此之外，只有5%的合作伙伴才允許批量上傳訂單，因為系統(tǒng)處理不了任何額外的負(fù)載。用于導(dǎo)入訂單的系統(tǒng)由十幾C+應(yīng)用程序構(gòu)成，30多個文件夾散布在3臺服務(wù)器上，在導(dǎo)入訂單的數(shù)據(jù)庫中，只存在有少量代碼。這些程序是十幾年前寫的，它們90%惟上的工作都是在不同的目錄之間移動文件，而目錄唯一的作用就是在處理期間隔離文件。通過進(jìn)一步研究，我們還發(fā)現(xiàn)對多個應(yīng)用程序進(jìn)行管理的代碼試圖訪問相同的文件。這所以出現(xiàn)這個現(xiàn)象，

3、完全是整個系統(tǒng)的組合方式造成的。文件終于各就各位并開始真正進(jìn)行和業(yè)務(wù)有關(guān)的處理時，我們發(fā)現(xiàn)應(yīng)用程序每次從文件中讀取一行并處理它。針對處理的每一行，應(yīng)用程序都執(zhí)行超過100個查詢來校驗產(chǎn)品代碼、現(xiàn)有庫存、價格水平以及其他幾個業(yè)務(wù)規(guī)則。我們重寫整個系統(tǒng)來利用SQL Server的批量導(dǎo)入功能。在重寫的階段1，我們刪除了所有的C+代碼和整個文件夾結(jié)構(gòu)，用單個文件夾來取而代之，用一個存儲過程對文件進(jìn)行BCP，并用另一個存儲過程處理文件導(dǎo)入之后的一切事情。在階段2，我們將BCP例程替換成一個SSIS包，它能并行處理多個文件，而且在處理多種數(shù)據(jù)格式時更靈活。完成階段1后，導(dǎo)入例程的效率已遠(yuǎn)遠(yuǎn)地超出合作伙伴

4、上傳文件的速度。文件上傳之后不到一分鐘的時間，所有數(shù)據(jù)便已導(dǎo)入并處理完成，訂單在倉庫那里排隊等待打包。完成階段2后，我們將訂單上傳服務(wù)擴(kuò)展到其他95%的合作伙伴。甚至最大的合作伙伴的訂單文件也能在文件上傳之后的15秒內(nèi)處理完畢。系統(tǒng)升級改造的直接結(jié)果是有利于擴(kuò)大客戶群。在新系統(tǒng)改造完畢之后的半年之內(nèi)，公司的業(yè)務(wù)增長超過400%以上。實例回放實例回放1. 導(dǎo)入和導(dǎo)出數(shù)據(jù)學(xué)習(xí)目標(biāo) 使用BCP(bulk copy program)將數(shù)據(jù)導(dǎo)出到文件 使用BCP從文件導(dǎo)入數(shù)據(jù) 使用BULK INSERT從文件導(dǎo)入數(shù)據(jù) 使用“SQL Server導(dǎo)入導(dǎo)出向?qū)А边M(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出1. 導(dǎo)入和導(dǎo)出數(shù)據(jù)批量復(fù)制

5、程序（BCP） BCP是將文件中的具有一定格式的數(shù)據(jù)導(dǎo)入SQL Server以及將表導(dǎo)出到文件最有效的方式。 BCP設(shè)計成導(dǎo)入和導(dǎo)出數(shù)據(jù)的一種非?？斓?、輕量級的解決方案。 若要在導(dǎo)入/導(dǎo)出過程中執(zhí)行轉(zhuǎn)換，或者執(zhí)行錯誤處理例程，那么應(yīng)該使用SQLServer Integration Services(SSIS)來進(jìn)行導(dǎo)入/導(dǎo)出。 使用BCP導(dǎo)出數(shù)據(jù)，運行BCP的帳戶只需要表或視圖上的SELECT權(quán)限。導(dǎo)入數(shù)據(jù)，需要SELECT, INSERT和ALTER TABLE權(quán)限。1. 導(dǎo)入和導(dǎo)出數(shù)據(jù)BCP是從命令行執(zhí)行的實用程序，語法如下：bcp database_name.owner.table_na

6、me | view_name | query in | out | queryout | format data_file -m max_errors -f format_file -e err_file -F first_row -L last_row -b batch_size -n -c -w -N -V (60 | 65 | 70) -6 -q -C code_page -t field_term -r row_term -i input_file -o output_file -a packet_size -S server_nameinstance_name -U login_id

7、 -P password -T -v -R -k -E -h hint ,.n 警告：大小寫敏感SQL Server數(shù)據(jù)導(dǎo)入導(dǎo)出工具數(shù)據(jù)導(dǎo)入導(dǎo)出工具BCP詳解詳解http:/ 導(dǎo)入和導(dǎo)出數(shù)據(jù)示例bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt n SHOTEK Tbcp AdventureWorks.HumanResources.Department in c:testdepartment.txt c SHOTEK U -Pbcp “SELECT Name, GroupName FROM HumanRes

8、ources.Department” queryout c:testdepartment.txt n SHOTEK -T說明：-n c參數(shù)互斥-n 使用數(shù)據(jù)的本機（數(shù)據(jù)庫）數(shù)據(jù)類型執(zhí)行大容量復(fù)制操作。此選項不提示輸入每一字段，它將使用本機值。-c 使用字符數(shù)據(jù)類型執(zhí)行大容量復(fù)制操作。此選項不提示輸入每一字段；它使用 char 作為存儲類型，不帶前綴，t（制表符）作為字段分隔符，n（換行符）作為行終止符。-S 開關(guān)指定要連接到的實例名稱。-T 開關(guān)指定一個可信的連接，將用執(zhí)行BCP時使用的Windows賬戶建立連接。-U P 可使用SQL Server登錄憑據(jù)。1. 導(dǎo)入和導(dǎo)出數(shù)據(jù)注意強制檢查約

9、束和觸發(fā)器強制檢查約束和觸發(fā)器用BCP將數(shù)據(jù)導(dǎo)入表時，觸發(fā)器和檢查約束默認(rèn)禁用。如果想強制檢查約束，并在導(dǎo)入期間激活觸發(fā)器，需要使用-h開關(guān)。如果導(dǎo)入時不禁用觸發(fā)器和檢查約束，就不需要ALTER TABLE權(quán)限。1. 導(dǎo)入和導(dǎo)出數(shù)據(jù)BULK INSERT命令BCP實用程序的缺點在于它是一個命令行程序。BULK INSERT命令提供了許多和BCP相同的選項，行為也基本相同，但存在以下2個區(qū)別： BULK INSERT不能導(dǎo)出數(shù)據(jù) BULK INSERT是T-SQL命令，不需要指定實例名或登錄憑據(jù)。BULK INSERT 詳解http:/ 導(dǎo)入和導(dǎo)出數(shù)據(jù)SQL Server導(dǎo)入和導(dǎo)出向?qū) BCP

10、和BULK INSERT命令提供了簡單 、輕量級的方式，通過使用文件來導(dǎo)入和導(dǎo)出數(shù)據(jù)。n 若想直接在來源和目的地之間導(dǎo)入和導(dǎo)出數(shù)據(jù)，同時應(yīng)用轉(zhuǎn)換和錯誤處理例程，則可以使用SQL Server Intergration Service(SSIS)的功能來構(gòu)建。n SSMS中的“SQL Server導(dǎo)入和導(dǎo)出向?qū)А碧峁┝薙SIS功能的一個子集，允許管理員在來源和目的地之間移動數(shù)據(jù)。n 向?qū)褂肧SIS能識別的任何數(shù)據(jù)源，包括EXCEL，ACCESS或者XML文件。n 向?qū)еС諳LEDB數(shù)據(jù)提供程序的任何數(shù)據(jù)源或目的地。n 向?qū)Р灰髷?shù)據(jù)源或目的地是SQLServer實例。n 向?qū)Э蓮亩鄠€表或文件移

11、動數(shù)據(jù)；BCP和BULK INSERT只能操作單個表、視圖或查詢。實訓(xùn)1 使用BCP導(dǎo)入和導(dǎo)出數(shù)據(jù)1.打開命令提示符窗口執(zhí)行以下命令bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt c S -T2.在記事本中打開department.txt文件查看文件內(nèi)容3.執(zhí)行以下命令bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt n S -T4.查看導(dǎo)出文件內(nèi)容。實訓(xùn)2 練習(xí)使用向?qū)?dǎo)入和導(dǎo)出數(shù)據(jù)將AdventureWorks數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出至一個EXCEL文件中本章回顧n BCP程序允許將數(shù)據(jù)從文件導(dǎo)入表，或者將數(shù)據(jù)從表導(dǎo)出到文件。n BULK INSERT是一個T-SQL命令，允許將數(shù)據(jù)從文件導(dǎo)入表。n “導(dǎo)入和導(dǎo)出向?qū)А笔褂肧SIS的一個子集在數(shù)