bcpÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB-LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£bcp¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö£¬Ò²ÄÜͨ¹ýSELECT FROMÓï¾ä¶Ô±í»òÊÓͼ½øÐйýÂ˺󵼳ö¡£ÔÚµ¼Èëµ¼³öÊý¾Ýʱ£¬¿ÉÒÔʹÓÃĬÈÏÖµ»òÊÇʹÓÃÒ»¸ö¸ñʽÎļþ½«ÎļþÖеÄÊý¾Ýµ¼Èëµ½Êý¾Ý¿â»ò½«Êý¾Ý¿âÖеÄÊý¾Ýµ¼³öµ½ÎļþÖС£ÏÂÃ潫ÏêϸÌÖÂÛÈçºÎÀûÓÃbcpµ¼Èëµ¼³öÊý¾Ý¡£
1. bcpµÄÖ÷Òª²ÎÊý½éÉÜ
bcp¹²ÓÐËĸö¶¯×÷¿ÉÒÔÑ¡Ôñ¡£
(1) µ¼Èë¡£
Õâ¸ö¶¯×÷ʹÓÃinÃüÁîÍê³É£¬ºóÃæ¸úÐèÒªµ¼ÈëµÄÎļþÃû¡£
(2) µ¼³ö¡£
Õâ¸ö¶¯×÷ʹÓÃoutÃüÁîÍê³É£¬ºóÃæ¸úÐèÒªµ¼³öµÄÎļþÃû¡£
(3) ʹÓÃSQLÓï¾äµ¼³ö¡£
Õâ¸ö¶¯×÷ʹÓÃqueryoutÃüÁîÍê³É£¬Ëü¸úoutÀàËÆ£¬Ö»ÊÇÊý¾ÝÔ´²»ÊDZí»òÊÓͼÃû£¬¶øÊÇSQLÓï¾ä¡£
(4) µ¼³ö¸ñʽÎļþ¡£
Õâ¸ö¶¯×÷ʹÓÃformatÃüÁîÍê³É£¬ºó¶ø¸ú¸ñʽÎļþÃû¡£
ÏÂÃæ½éÉÜһЩ³£ÓõÄÑ¡Ï
-f format_file
format_file±íʾ¸ñʽÎļþÃû¡£Õâ¸öÑ¡ÏîÒÀÀµÓÚÉÏÊöµÄ¶¯×÷£¬Èç¹ûʹÓõÄÊÇin»òout£¬format_file±íʾÒѾ´æÔڵĸñʽÎļþ£¬Èç¹ûʹÓõÄÊÇformatÔò±íʾÊÇÒªÉú³ÉµÄ¸ñʽÎļþ¡£
-x
Õâ¸öÑ¡ÏîÒªºÍ-f format_fileÅäºÏʹÓã¬ÒÔ±ãÉú³Éxml¸ñʽµÄ¸ñʽÎļþ¡£
-F first_row
Ö¸¶¨´Ó±»µ¼³ö±íµÄÄÄÒ»Ðе¼³ö£¬»ò´Ó±»µ¼ÈëÎļþµÄÄÄÒ»Ðе¼Èë¡£
-L last_row
Ö¸¶¨±»µ¼³ö±íÒªµ¼µ½ÄÄÒ»ÐнáÊø£¬»ò´Ó±»µ¼ÈëÎļþµ¼Êý¾Ýʱ£¬µ¼µ½ÄÄÒ»ÐнáÊø¡£
-c
ʹÓÃcharÀàÐÍ×öΪ´æ´¢ÀàÐÍ£¬Ã»ÓÐǰ׺ÇÒÒÔ"\t"×öΪ×ֶηָî·û£¬ÒÔ"\n"×öΪÐзָî·û¡£
-w
ºÍ-cÀàËÆ£¬Ö»Êǵ±Ê¹ÓÃUnicode×Ö·û¼¯¿½±´Êý¾ÝʱʹÓã¬ÇÒÒÔnchar×öΪ´æ´¢ÀàÐÍ¡£
-t field_term
Ö¸¶¨×Ö·û·Ö¸î·û£¬Ä¬ÈÏÊÇ"\t"¡£
-r row_term
Ö¸¶¨Ðзָî·û£¬Ä¬ÈÏÊÇ"\n"¡£
-S server_name[ \instance_name]
Ö¸¶¨ÒªÁ¬½ÓµÄSQL Server·þÎñÆ÷µÄʵÀý£¬Èç¹ûδָ¶¨´ËÑ¡ÏbcpÁ¬½Ó±¾»úµÄSQL ServerĬÈÏʵÀý¡£Èç¹ûÒªÁ¬½Óij̨»úÆ÷ÉϵÄĬÈÏʵÀý£¬Ö»ÐèÒªÖ¸¶¨»úÆ÷Ãû¼´¿É¡£
-U login_id
Ö¸¶¨Á¬½ÓSQL SeverµÄÓû§Ãû¡£
-P password
Ö¸¶¨Á¬½ÓSQL ServerµÄÓû§ÃûÃÜÂë¡£
-T
Ö¸¶¨bcpʹÓÃÐÅÈÎÁ¬½ÓµÇ¼SQL Server¡£Èç¹ûδָ¶¨-T£¬±ØÐëÖ¸¶¨-UºÍ-P¡£
-k
Ö¸¶¨¿ÕÁÐʹÓÃnullÖµ²åÈ룬¶ø²»ÊÇÕâÁеÄĬÈÏÖµ¡£
2. ÈçºÎʹÓÃbcpµ¼³öÊý¾Ý
(1) ʹÓÃbcpµ¼³öÕû¸ö±í»òÊÓͼ¡£
bcp AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"password" --ʹÓÃÃÜÂëÁ¬½Ó»òbcp AdventureWorks.sales.currency out c:\currency1.txt -c -T --ʹÓÃÐÅÈÎÁ¬½Ó
ÏÂÃæÊÇÉÏÊöÃüÁîÖ´ÐкóµÄÊä³ö½á¹û
Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)
ÏÂÃæÊÇcurrency1.txtµÄ²¿·ÖÄÚÈÝ
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
... ... ...
... ... ...
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000
ÔÚʹÓÃÃÜÂëµÇ¼ʱÐèÒª½«-UºóµÄÓû§ÃûºÍ-PºóµÄÃÜÂë¼ÓÉÏË«ÒýºÅ¡£
×¢£ºbcp³ýÁË¿ÉÒÔÔÚ¿ØÖÆִ̨ÐÐÍ⣬»¹¿ÉÒÔͨ¹ýµ÷ÓÃSQL ServerµÄÒ»¸öϵͳ´æ´¢¹ý³Ìxp_cmdshellÒÔSQLÓï¾äµÄ·½Ê½ÔËÐÐbcp¡£ÈçÉÏÊöµÚÒ»ÌõÃüÁî¿É¸ÄдΪ
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"password"'
Ö´ÐÐxp_cmdshellºó£¬·µ»ØÐÅÏ¢ÒÔ±íµÄÐÎʽÊä³ö¡£ÎªÁË¿ÉÒÔ·½±ãµØÔÚSQLÖÐÖ´ÐÐbcp£¬ÏÂÃæµÄÃüÁʹÓÃxp_cmdshellÖ´ÐÐbcpÃüÁî¡£
(2) ¶ÔÒªµ¼³öµÄ±í½øÐйýÂË¡£
bcp²»½ö¿ÉÒÔ½ÓÊܱíÃû»òÊÓͼÃû×öΪ²ÎÊý£¬Ò²¿ÉÒÔ½ÓÊÜSQL×öΪ²ÎÊý¡£Í¨¹ýSQLÓï¾ä¿ÉÒÔ¶ÔÒªµ¼³öµÄ±í½øÐйýÂË£¬È»ºóµ¼³ö¹ýÂ˺óµÄ¼Ç¼¡£
EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:\currency2.txt -c -U"sa" -P"password"'
bcp»¹¿ÉÒÔͨ¹ý¼òµ¥µØÉèÖÃÑ¡Ïî¶Ôµ¼³öµÄÐнøÐÐÏÞÖÆ¡£
ÕâÌõÃüÁîʹÓÃÁËÁ½¸ö²ÎÊý-F 10ºÍ-L 13£¬±íʾ´ÓSE
EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:\currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'
LECT TOP 20 * FROM AdventureWorks.sales.currencyËù²é³öÀ´µÄ½á¹ûÖÐÈ¡µÚ10Ìõµ½13Ìõ¼Ç¼½øÐе¼³ö¡£
3. ÈçºÎʹÓÃbcpµ¼³ö¸ñʽÎļþ
bcp²»½ö¿ÉÒÔ¸ù¾Ý±í¡¢ÊÓͼµ¼Èëµ¼³öÊý¾Ý£¬»¹¿ÉÒÔÅäºÏ¸ñʽÎļþ¶Ôµ¼Èëµ¼³öÊý¾Ý½øÐÐÏÞÖÆ¡£¸ñʽÎļþÒÔ´¿Îı¾ÎļþÐÎʽ´æÔÚ£¬·ÖΪһ°ã¸ñʽºÍxml¸ñʽ¡£Óû§¿ÉÒÔÊÖ¹¤±àд¸ñʽÎļþ£¬Ò²¿ÉÒÔͨ¹ýbcpÃüÁî¸ù¾Ý±í¡¢ÊÓͼ×Ô¶¯Éú³É¸ñʽÎļþ¡£
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:\currency_format1.fmt -c -T'
ÉÏÊöÃüÁcurrency±íµÄ½á¹¹Éú³ÉÁËÒ»¸ö¸ñʽÎļþcurrency_format1.fmt£¬ÏÂÃæÊÇÕâ¸ö¸ñʽÎļþµÄÄÚÈÝ¡£
9.0
3
1 SQLCHAR 0 6 "\t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "\r\n" 3 ModifiedDate
Õâ¸ö¸ñʽÎļþ¼Ç¼ÁËÕâ¸ö±íµÄ×ֶΣ¨¹²3¸ö×ֶΣ©ÀàÐÍ¡¢³¤¶È¡¢×Ö·ûºÍÐзָî·ûºÍ×Ö¶ÎÃûµÈÐÅÏ¢¡£
bcp»¹¿ÉÒÔͨ¹ý-xÑ¡ÏîÉú³Éxml¸ñʽµÄ¸ñʽÎļþ¡£
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency format nul -f c:\currency_format2.fmt -x -c -T'
xml¸ñʽÎļþËùÃèÊöµÄÄÚÈݺÍÆÕͨ¸ñʽÎļþËùÃèÊöµÄÄÚÈÝÍêÈ«Ò»Ñù£¬Ö»ÊǸñʽ²»Í¬¡£
4. ÈçºÎʹÓÃbcpµ¼ÈëÊý¾Ý
bcp¿ÉÒÔͨ¹ýinÃüÁÉÏÃæËùµ¼³öµÄcurrency1.txtºÍcurrency2.txtÔÙÖØе¼Èëµ½Êý¾Ý¿âÖУ¬ÓÉÓÚcurrencyÓÐÖ÷¼ü£¬Òò´ËÎÒÃǽ«¸´ÖÆÒ»¸öºÍcurrencyµÄ½á¹¹ÍêÈ«Ò»ÑùµÄ±í¡£
SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency
½«Êý¾Ýµ¼Èëµ½currency1±íÖÐ
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:\currency1.txt -c -T'
µ¼ÈëÊý¾ÝҲͬÑù¿ÉÒÔʹÓÃ-FºÍ-LÑ¡ÏîÀ´Ñ¡Ôñµ¼ÈëÊý¾ÝµÄ¼Ç¼ÐС£
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:\currency1.txt -c -F 10 -L 13 -T'
ÔÚµ¼ÈëÊý¾Ýʱ¿ÉÒÔ¸ù¾ÝÒѾ´æÔڵĸñʽÎļþ½«Âú×ãÌõ¼þµÄ¼Ç¼µ¼Èëµ½Êý¾Ý¿âÖУ¬²»Âú×ãÔò²»µ¼Èë¡£ÈçÉÏÊöµÄ¸ñʽÎļþÖеĵÚÈý¸ö×ֶεÄ×Ö·û³¤¶ÈÊÇ24£¬Èç¹ûij¸öÎı¾ÎļþÖеÄÏàÓ¦×ֶεij¤¶È³¬¹ý24£¬ÔòÕâÌõ¼Ç¼½«²»±»µ¼Èëµ½Êý¾Ý¿âÖУ¬ÆäËüÂú×ãÌõ¼þµÄ¼Ç¼Õý³£µ¼Èë¡£
ʹÓÃÆÕͨµÄ¸ñʽÎļþ
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:\currency1.txt -F 10 -L 13 -c -f c:\currency_format1.fmt -T'
ʹÓÃxml¸ñʽµÄ¸ñʽÎļþ
EXEC master..xp_cmdshell 'bcp AdventureWorks.sales.currency1 in c:\currency1.txt -F 10 -L 13 -c -x -f c:\currency_format2.fmt -T'
×ܽá
bcpÃüÁîÊÇSQL ServerÌṩµÄÒ»¸ö¿ì½ÝµÄÊý¾Ýµ¼Èëµ¼³ö¹¤¾ß¡£Ê¹ÓÃËü²»ÐèÒªÆô¶¯ÈκÎͼÐιÜÀí¹¤¾ß¾ÍÄÜÒÔ¸ßЧµÄ·½Ê½µ¼Èëµ¼³öÊý¾Ý¡£µ±È»£¬ËüÒ²¿ÉÒÔͨ¹ýxp_cmdshellÔÚSQLÓï¾äÖÐÖ´ÐУ¬Í¨¹ýÕâÖÖ·½Ê½¿ÉÒÔ½«Æä·Åµ½¿Í»§¶Ë³ÌÐòÖУ¨Èçdelphi¡¢c#µÈ£©ÔËÐУ¬ÕâÒ²ÊÇʹ¿Í»§¶Ë³ÌÐò¾ßÓÐÊý¾Ýµ¼Èëµ¼³ö¹¦Äܵķ½·¨Ö®Ò»¡£