ºìÁªLinuxÃÅ»§
Linux°ïÖú

SQL ServerÊý¾Ýµ¼Èëµ¼³ö¹¤¾ßBCPÏê½â

·¢²¼Ê±¼ä:2006-10-15 00:34:10À´Ô´:ºìÁª×÷Õß:williamEXE
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#µÈ£©ÔËÐУ¬ÕâÒ²ÊÇʹ¿Í»§¶Ë³ÌÐò¾ßÓÐÊý¾Ýµ¼Èëµ¼³ö¹¦Äܵķ½·¨Ö®Ò»¡£
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 0 ÌõÆÀÂÛ