SQL 2005 Tips Bulk data transfer sample
CREATE PROC [dbo].[UP_EC_JOB_UpdateAddressType]
(
@Count INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TransactionNumber INT
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT TOP(@Count) TransactionNumber
from dbo.CustomerShippingInfo WITH(NOLOCK)
WHERE AddressType IS NULL
OPEN @Cursor
FETCH NEXT from @Cursor INTO @TransactionNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE dbo.CustomerShippingInfo
SET AddressType = 1
WHERE TransactionNumber = @TransactionNumber
FETCH NEXT from @Cursor INTO @TransactionNumber
END
CLOSE @Cursor
DEALLOCATE @Cursor
END
Ïà¹ØÎĵµ£º
BUILTIN\Administrators
-- Õâ¸öÊÇAdmin×éµÄȨÏÞ,ĬÈÏÇé¿öϱ¾µØAdmin¶¼Á¥ÊôÓÚÕâ¸ö×é,¾ßÓбȽϴóµÄȨÏÞ,
-- ×÷ΪWindows¼¯³ÉµÇ¼,Èç¹ûʹÓõÄÊǹÜÀíÔ±,¾Í»áʹÓõ½Ëü,
HC811\ASPNET
-- Õâ¸öÊÇASPNETÓ¦ÓõķÃÎÊÕʺÅ×é,
HC811\SQLServer2005MSFTEUser$HC811$MSSQLSERVER
-- Õâ¸öÊÇÈ«ÎÄË÷ÒýµÄ·ÃÎÊÕʺ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ʵ¼ÊÓ¦ÓÃÖÐÎÒÃÇ¿ÉÒÔͨ¹ýsum()ͳ¼Æ³ö×éÖеÄ×ܼƻòÕßÊÇÀÛ¼ÓÖµ£¬¾ßÌåʾÀýÈçÏ£º
......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐʱºò±¨±íÉÏÃæÐèÒªÏÔʾ¸Ã±Ê²Ù×÷µÄÉÏÒ»²½Öè»òÕßÏÂÒ»²½ÖèµÄÏêϸÐÅÏ¢£¬Õâ¸öʱºò¿ ......
----start
·²ÊÇÖªµÀÊý¾Ý¿âµÄÈ˶¼ÖªµÀSQL£¬·²ÊǶÔSQLÓÐÒ»µãÁ˽âµÄÈ˶¼¾õµÃSQLºÜ¼òµ¥£¬·²ÊÇÓÐÕâÖָоõµÄÈ˶¼ÊÇSQLµÃ³õ¼¶Óû§£¬ÒòΪËûѧ»áÁËÔö²éɾ¸Ä¾ÍÒÔΪÕâ¾ÍÊÇSQLµÄÈ«²¿¡£Ä¿Ç°µÄ´ó²¿·ÖÓ¦ÓÃÈí¼þ¶¼ÊÇÒÔÊý¾Ý¿âΪÖÐÐÄ£¬Ëæ×ÅÈí¼þµÄÔËÐУ¬Êý¾ÝÁ¿»áÔ½À´Ô½´ó¡£ÈçºÎÓüò½à¡¢¸ßЧµÄSQLÓï¾ä²Ù×÷Êý¾ÝÏÔµÃÔ½À ......
1¡¢Ñ»·²åÈë²âÊÔÊý¾Ý£º
SQL>
declare
maxrecords constant int:=100;
i int :=1;
Begin
for i in 1..maxrecords loop
--²åÈëÊý¾Ý
Insert into TEST.EXAMPLE(name,phone,address) values('·ÖÒ³²âÊÔÊý¾Ý....','ÁªÏµµç»°','ÁªÏµµØÖ·');
end loop;
commit;
end;
´Ë´¦EXAMPLE±í¶¨ÒåΪ(ID number,name ......