SqlServer´óÅúÁ¿µ¼Èëµ¼³öÊý¾Ý
BULK INSERT
¡¡¡¡ÔÚSQL ServerÖУ¬BULK INSERTÊÇÓÃÀ´½«ÍⲿÎļþÒÔÒ»ÖÖÌØ¶¨µÄ¸ñʽ¼ÓÔØµ½Êý¾Ý¿â±íµÄT-SQLÃüÁî¡£¸ÃÃüÁîʹ¿ª·¢ÈËÔ±Äܹ»Ö±½Ó½«Êý¾Ý¼ÓÔØµ½Êý¾Ý¿â±íÖУ¬¶ø²»ÐèҪʹÓÃÀàËÆÓÚIntegration ServicesÕâÑùµÄÍⲿ³ÌÐò¡£ËäÈ»BULK INSERT²»ÔÊÐí°üº¬Èκθ´ÔÓµÄÂß¼»òת»»£¬µ«Äܹ»ÌṩÓë¸ñʽ»¯Ïà¹ØµÄÑ¡Ï²¢¸æËßÎÒÃǵ¼ÈëÊÇÈçºÎʵÏֵġ£BULK INSERTÓÐÒ»¸öʹÓÃÏÞÖÆ£¬¾ÍÊÇÖ»Äܽ«Êý¾Ýµ¼ÈëSQL Server¡£
¡¡¡¡²åÈëÊý¾ÝÏÂÃæµÄÀý×ÓÄÜÈÃÎÒÃǸüºÃµÄÀí½âÈçºÎʹÓÃBULK INSERTÃüÁî¡£Ê×ÏÈ£¬ÎÒÃÇÀ´´´½¨Ò»¸öÃûΪSalesµÄ±í£¬ÎÒÃǽ«Òª°ÑÀ´×ÔÎı¾ÎļþµÄÊý¾Ý²åÈëµ½Õâ¸ö±íÖС£
¡¡¡¡CREATE TABLE [dbo].[Sales]
¡¡¡¡(
¡¡¡¡[SaleID] [int],
¡¡¡¡[Product] [varchar](10) NULL,
¡¡¡¡[SaleDate] [datetime] NULL,
¡¡¡¡[SalePrice] [money] NULL
¡¡¡¡)
µ±ÎÒÃÇʹÓÃBULK INSERTÃüÁîÀ´²åÈëÊý¾Ýʱ£¬²»ÒªÆô¶¯Ä¿±ê±íÖеĴ¥·¢Æ÷£¬ÒòΪ´¥·¢Æ÷»á¼õ»ºÊý¾Ýµ¼ÈëµÄ½ø³Ì¡£
¡¡¡¡ÔÚÏÂÒ»¸öÀý×ÓÖУ¬ÎÒÃǽ«ÔÚSales±íÉÏ´´½¨´¥·¢Æ÷£¬ÓÃÀ´´òÓ¡²åÈëµ½±íÖеļǼµÄÊýÁ¿¡£
¡¡¡¡CREATE TRIGGER tr_Sales
¡¡¡¡ON Sales
¡¡¡¡FOR INSERT
¡¡¡¡AS
¡¡¡¡BEGIN
¡¡¡¡PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.'
¡¡¡¡END
ÕâÀïÎÒÃÇÑ¡ÔñÎı¾Îļþ×÷ΪԴÊý¾ÝÎļþ£¬Îı¾ÎļþÖеÄֵͨ¹ý¶ººÅ·Ö¸î¿ª¡£¸ÃÎļþ°üº¬1000Ìõ¼Ç¼£¬¶øÇÒÆä×ֶκÍSales±íµÄ×Ö¶ÎÖ±½Ó¹ØÁª¡£ÓÉÓÚ¸ÃÎı¾ÎļþÖеÄÖµÊÇÓɶººÅ·Ö¸î¿ªµÄ£¬ÎÒÃÇÖ»ÐèÒªÖ¸¶¨FIELDTERMINATOR¼´¿É¡£×¢Ò⣬µ±ÏÂÃæÕâÌõÓï¾äÔËÐÐʱ£¬ÎÒÃǸոմ´½¨µÄ´¥·¢Æ÷²¢Ã»ÓÐÆô¶¯£º
¡¡¡¡BULK INSERT Sales from 'c:SalesText.txt' WITH (FIELDTERMINATOR = ',')
¡¡¡¡µ±ÎÒÃÇÒªµÄÊý¾ÝÁ¿·Ç³£´óʱ£¬ÓÐʱºò¾ÍÐèÒªÆô¶¯´¥·¢Æ÷¡£ÏÂÃæµÄ½Å±¾Ê¹ÓÃÁËFIRE_TRIGGERSÑ¡ÏîÀ´Ö¸Ã÷ÔÚÄ¿±ê±íÉϵÄÈκδ¥·¢Æ÷¶¼Ó¦µ±Æô¶¯£º
¡¡¡¡BULK INSERT Sales from 'c:SalesText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)
¡¡¡¡ÎÒÃÇ¿ÉÒÔʹÓÃBATCHSIZEÖ¸ÁîÀ´ÉèÖÃÔÚµ¥¸öÊÂÎñÖпÉÒÔ²åÈëµ½±íÖеļǼµÄÊýÁ¿¡£
Ïà¹ØÎĵµ£º
ÓÃwindowsÕ˺ݹÊÇ¿ÉÒԵǼµÄ¡£
²éÕÒÎÊÌâµÄ¹ý³Ì£º
µÚÒ»²½£º Æô¶¯ËùÓÐÓëSQLÓйصķþÎñ£¬ÎÊÌâÒÀ¾É£»
µÚ¶þ²½£º ²é¿´windows·À»ðǽ£¬±»Ä¬ÈÏÆô¶¯ÁË£¬²»Æô¶¯Ñ¡ÔñÏî±»Disabled¡£Á½¸öÌáʾ“ÓÉÓÚ°²È«¿¼ÂÇ,ijЩÉèÖÃÓÉ×é²ßÂÔ¿ØÖÆ”“Windows·À»ðǽÕýÔÚʹÓÃÄúµÄÓòÉèÖÔ£¬ÏÈÓÃgpedit.msc´ò×é²ßÂÔ±à¼Æ÷£¬» ......
UnionĬÈÏÊDz»ÄÜÓÐorder by×Ó¾ä
Èç¹ûÐèÒªÅÅÐò£¬ÔòÖ»ÄÜÈçÏÂʵÏÖ
select * from (select top 100 PERCENT * from ±íÃû order by ×Ö¶ÎÃû) a
union all
select * from (select top 100 PERCENT * from ±íÃû ......
NVL(Expr1,Expr2)Èç¹ûExpr1ΪNULL£¬·µ»ØExpr2µÄÖµ£¬·ñÔò·µ»ØExpr1µÄÖµ
NVL2(Expr1,Expr2,Expr3)Èç¹ûExpr1ΪNULL£¬·µ»ØExpr2µÄÖµ£¬·ñÔò·µ»ØExpr3µÄÖµ
NULLIF(Expr1,Expr2)Èç¹ûExpr1ºÍExpr2µÄÖµÏàµÈ£¬·µ»ØNULL£¬·ñÔò·µ»ØExpr1µÄÖµ ......
if exists (select * from dbo.sysobjects where name='SplitStr' )
drop FUNCTION SplitStr
go
CREATE FUNCTION SplitStr (@splitString varchar(8000), @separate varchar(10))
RETURNS @returnTable ......
²éѯnorthwindÊý¾Ý¿âÖÐordersÖеÄ10µ½20Ìõ¼Ç¼
select top 10 * from orders
where orderid > ( select max(orderid) from (select top 10 orderid from orders order by orderid) as t&nbs ......