SQLServer´óÊý¾ÝÁ¿²åÈëBULK INSERT
SQLSERVER ´óÊý¾ÝÁ¿²åÈëÃüÁ
BULK INSERTÊÇSQLSERVERÖÐÌṩµÄÒ»Ìõ´óÊý¾ÝÁ¿µ¼ÈëµÄÃüÁËüÔËÓÃDTS(SSIS)µ¼ÈëÔÀí£¬¿ÉÒÔ´Ó±¾µØ»òÔ¶³Ì·þÎñÆ÷ÉÏÅúÁ¿µ¼ÈëÊý¾Ý¿â»òÎļþÊý¾Ý¡£ÅúÁ¿²åÈëÊÇÒ»¸ö¶ÀÁ¢µÄ²Ù×÷£¬ÓŵãÊÇЧÂʷdz£¸ß¡£È±µãÊdzöÏÖÎÊÌâºó²»¿ÉÒԻعö¡£
¡¡¡¡BULK INSERTÊÇÓÃÀ´½«ÍⲿÎļþÒÔÒ»ÖÖÌض¨µÄ¸ñʽ¼ÓÔص½Êý¾Ý¿â±íµÄT-SQLÃüÁî¡£¸ÃÃüÁîʹ¿ª·¢ÈËÔ±Äܹ»Ö±½Ó½«Êý¾Ý¼ÓÔص½Êý¾Ý¿â±íÖУ¬¶ø²»ÐèҪʹÓÃÀàËÆÓÚIntegration ServicesÕâÑùµÄÍⲿ³ÌÐò¡£ËäÈ»BULK INSERT²»ÔÊÐí°üº¬Èκθ´ÔÓµÄÂß¼»òת»»£¬µ«Äܹ»ÌṩÓë¸ñʽ»¯Ïà¹ØµÄÑ¡Ï²¢¸æËßÎÒÃǵ¼ÈëÊÇÈçºÎʵÏֵġ£BULK INSERTÓÐÒ»¸öʹÓÃÏÞÖÆ£¬¾ÍÊÇÖ»Äܽ«Êý¾Ýµ¼ÈëSQL Server¡£
²åÈëÊý¾ÝÏÂÃæµÄÀý×ÓÄÜÈÃÎÒÃǸüºÃµÄÀí½âÈçºÎʹÓÃBULK INSERTÃüÁî¡£Ê×ÏÈ£¬ÎÒÃÇÀ´´´½¨Ò»¸öÃûΪSalesµÄ±í£¬ÎÒÃǽ«Òª°ÑÀ´×ÔÎı¾ÎļþµÄÊý¾Ý²åÈëµ½Õâ¸ö±íÖС£
±¾µØÊý¾Ý²Ù×÷£º
µ±ÎÒÃÇʹÓÃBULK INSERTÃüÁîÀ´²åÈëÊý¾Ýʱ£¬´´½¨µÄ±íÓÐÎÞ´¥·¢Æ÷ÐèÒª²»Í¬µÄÅäÖã¬ÎÒÃÇ·Ö±ð¾ÙÀýÀ´¿´£º
¡¾AûÓд¥·¢Æ÷µÄ±í¡¿
¡¡¡¡CREATE TABLE [dbo].[Sales]
¡¡¡¡(
¡¡¡¡ [SaleID] [int],
¡¡¡¡ [Product] [varchar](10) NULL,
¡¡¡¡ [SaleDate] [datetime] NULL,
¡¡¡¡ [SalePrice] [money] NULL
¡¡¡¡)
¡¾BÓд¥·¢Æ÷µÄ±í¡¿
¡¡¡¡»¹ÊÇÔÚÉÏÃæ±íµÄ»ù´¡ÉÏ£¬ÎÒÃÇ´´½¨´¥·¢Æ÷£¬ÓÃÀ´´òÓ¡²åÈëµ½±íÖеļǼµÄÊýÁ¿¡£
¡¡¡¡CREATE TRIGGER tr_Sales
¡¡¡¡ON Sales
¡¡¡¡FOR INSERT
¡¡¡¡AS
¡¡¡¡BEGIN
¡¡¡¡PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.'
¡¡¡¡END
ÕâÀïÎÒÃÇÑ¡ÔñÎı¾Îļþ×÷ΪԴÊý¾ÝÎļþ£¬Îı¾ÎļþÖеÄֵͨ¹ý¶ººÅ·Ö¸î¿ª¡£¸ÃÎļþ°üº¬1000Ìõ¼Ç¼£¬¶øÇÒÆä×ֶκÍSales±íµÄ×Ö¶ÎÖ±½Ó¹ØÁª¡£ÓÉÓÚ¸ÃÎı¾ÎļþÖеÄÖµÊÇÓɶººÅ·Ö¸î¿ªµÄ£¬ÎÒÃÇÖ»ÐèÒªÖ¸¶¨FIELDTERMINATOR¼´¿É¡£×¢Ò⣬µ±ÏÂÃæÕâÌõÓï¾äÔËÐÐʱ£¬ÎÒÃǸոմ´½¨µÄ´¥·¢Æ÷²¢Ã»ÓÐÆô¶¯£º
A£ºÃ»Óд¥·¢Æ÷µÄ²Ù×÷
BULK INSERT Sales from 'C:\Users\Administrator\Desktop\Sales.txt' WITH (FIELDTERMINATOR = ',')
B£ºÓд¥·¢Æ÷µÄ²Ù×÷
¡¡¡¡µ±ÎÒÃÇÒªµÄÊý¾ÝÁ¿·Ç³
Ïà¹ØÎĵµ£º
sqlserver£ºupdate Table_A set Table_A.col1 = (select Table_B.col1 from Table_B where Table_A.col2 = Table_B.col2)
Access: UPDATE Table_ A, Table_B SET Table_ A.×Ö¶Î2 = Table_ B.×Ö¶Î2
WHERE Table_ A.񅧏=Table_ A.񅧏;
×Ô¼º¼ÇÏ£¬ÌáÐÑ×Ô¼º ......
ÏÂÎçÐèÒªÅäÖñ¾»ú°²×°µÄmssqlÊý¾Ý¿âÄܹ»ÔÚÔ¶³Ì·ÃÎÊ£¬ÔÚÍøÉÏËÑË÷µÄ·½·¨£ºhttp://weste.net/2009/5-20/21244518910.html
°´ÕÕÕâ¸ö²½ÖèÒ»²½Ò»²½ÅäÖÃÏÂȥȴ·¢ÏÖÈÔÈ»²»ÄÜ·ÃÎÊ¡£Èç¹ûÅäÖÃprotocolµÄʱºòlisten all ÉèÖÃΪno£¬ÖØÆômssql·þÎñϵͳ»áÌáʾmssql·þÎñ×Ô¶¯Í£Ö¹£»Èç¹ûÉèÖÃΪyes¿ÉÒÔÆô¶¯È´ÔÚnetstatÁбíÖÐÎÞ·¨¼ì²éµ½¶Ô1433¶ ......
sqlserverÊý¾Ý¿â£ºjavaÁ¬½Ósqlserver2005Êý¾Ý¿âÐĵÃÌå»á
Ê×ÏȵÃÏÂÔØÇý¶¯³ÌÐòµ½Î¢ÈíÍøÕ¾ÏÂÔØMicrosoft SQL Server 2005 JDBC Driver 1.2¡¡¡¡½âѹMicrosoft SQL Server 2005 jdbc driver1.2.exe
µÃµ½sqljdbc.jar£¬ÓõÃʱºòÖ±½Ó¼Óµ½classpathÖÐÈ¥.
¡¡ÉèÖÃSQL Server·þÎñÆ÷
·ÀÖ¹³öÏÖ
com.mi ......
SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
2008-01-23 15:47
SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ2008-01-15 15:51SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
SQL ServerÖÐÎÄ°æµÄĬÈϵÄÈÕÆÚ×Ö¶Îdatetime¸ñʽÊÇyyyy-mm-dd Thh:mm:ss.mmm
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
ÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄÜ ......
ÓÐЩ³£¼ûµÄÎÊÌâÔÚÂÛ̳Öв»¶Ï³öÏÖ£¬²»·ÁÕûÀíһϡ£
ÒÔÏÂÓï¾äÊÇÔÚSQLServer2005ÉÏʵÏֵģ¬Ò»Ð©Óï¾äÎÞ·¨ÔÚSS2000ÉÏÖ´ÐС£
ÓÐÓÃÖ¸ÊýÊÇÎÒ¸ù¾ÝÕâ¸öÎÊÌâµÄ³£¼û³Ì¶È´òµÄ·Ö£¬½ö¹©²Î¿¼¡£Êµ¼ÊÉÏ£¬µ±ÄãÓöµ½ÁËÕâ¸öÎÊÌ⣬Õâ¸öÎÊÌâÄÄÅÂÔÙÉÙ¼û£¬½â¾ö·½°¸Ò²ÊǷdz£ÓÐÓõġ£
1. Éú³ÉÈô¸ÉÐмǼ
ÓÐÓÃÖ¸Êý£º¡ï¡ï¡ï¡ï¡ï
³£¼ûµÄÎÊÌâÀàÐÍ£º¸ù¾Ý ......