SQL´æ´¢¹ý³Ì²âÊÔ£¨5£©——´ÓT
ÎÊÌâ
ÈçºÎÈÃT-SQL²âÊÔÌ×¼þ°Ñ²âÊÔÓÃÀý½á¹ûÖ±½ÓдÈëÎı¾Îļþ
Éè¼Æ
ʹÓÃActiveX¼¼ÊõʵÀý»¯Ò»¸öFileSystemObject¶ÔÏó£¬È»ºóͨ¹ýOpenTextFile()ºÍWriteLine()·½·¨Ö±½Ó°Ñ²âÊÔ½á¹ûдÈëÎļþ¡£
·½°¸
declare @fsoHandle int,@fileID int
exec sp_OACreate 'Scripting.FileSystemObject',@fsoHandle out
exec sp_OAMethod @fsohandle,'OpenTextFile',@fileID out,
'C:\pathToResults\Results.txt',8,1
——Ö÷²âÊÔÑ»·
if(@result = @expected)
exec sp_OAMethod @fileID,'WriteLine',null,'Pass'
else
exec sp_OAMethod @fileID,'WriteLine',null,'FAIL'
——Ö÷²âÊÔÑ»·½áÊø
exec sp_OADestroy @fileID
exec sp_OADestroy @fsoHandle
ÎÒÃÇÐèÒªÓÐÒ»¸öÎļþ¾ä±úºÍÒ»¸öÎļþID£¬ËüÃǵÄÀàÐͶ¼ÊÇInt£¬SQL ServerÓÐÒ»¸ö×÷sp_OACreate()µÄ´æ´¢¹ý³Ì¿ÉÒÔʵÀý»¯ActiveX¶ÔÏó¡£sp_OACreate()½ÓÊÜÒ»¸ö×Ö·û´®×÷Ϊ´«ÈëµÄ²ÎÊý£¬Õâ¸ö×Ö·û´®¾ÍÊÇÒª´´½¨µÄActiveX¶ÔÏóµÄÃû×Ö£¬²¢ÇÒÒÔout²ÎÊýµÄÐÎʽ·µ»ØÒ»¸öÒÑ´´½¨¶ÔÏóµÄÒýÓã¬Õâ¸öÒýÓõÄÀàÐÍÊÇint¡£¶ÔÓÚScripting.FileSystemObjectµÄÇé¿öÀ´Ëµ£¬·µ»ØÖµÊǹØÓÚÎļþ¾ä±úµÄÒ»¸öÒýÓ᣽ÓÏÂÀ´£¬¿ÉÒÔͨ¹ýµ÷ÓÃsp_OAMethod()·½·¨´ò¿ªÎļþ¡£ÔÚ±¾ÀýÖУ¬µÚÒ»¸ö²ÎÊýÊÇsp_OACreate()°Ñ´´½¨µÄ¾ä±ú£¬µÚ¶þ¸ö²ÎÊýÊÇÎÒÃÇÏëҪʹÓõķ½·¨µÄÃû³Æ£¬µÚÈý¸ö²ÎÊýÊÇÓÃÓÚ±£´æ·µ»ØµÄÎļþ¾ä±úµÄ±äÁ¿£¬µÚËĸö²ÎÊýÖ¸¶¨ÎļþµÄÎïÀíÃû³Æ£¬µÚÎå¸ö²ÎÊýÊÇ¿ÉÑ¡µÄ£¬ËüÖ¸¶¨Ê¹ÓõÄIOģʽ¡£
1£ºÒÔÖ»¶Á·½Ê½´ò¿ªÎļþ£¨Ä¬ÈÏ£©¡£
2£ºÒÔд·½Ê½´ò¿ªÎļþ¡£
3£ºÒÔ×·¼Ó·½Ê½´ò¿ªÎļþ
µÚÁù¸ö²ÎÊýÊÇ¿ÉÑ¡µÄ£¬ËüÊÇÒ»¸ö´´½¨±êʶ£¬ÓÃÀ´Ö¸¶¨µ±¸ø¶¨Îļþ²»´æÔÚµÄʱºòÊÇ·ñ´´½¨Ò»¸öеÄÎļþ¡£
0£º²»´´½¨ÐÂÎļþ£¨Ä¬ÈÏ£©¡£
1£º´´½¨Ò»¸öÐÂÎļþ¡£
µÚ°Ë¸öº¦ÊýÒ²ÊÇ¿ÉÑ¡µÄ£¬ËüÊÇÒ»¸ö¸ñʽ±êʶ£¬ÓÃÀ´Ö¸¶¨×Ö·ûµÄ±àÂ뷽ʽ¡£
0£ºÒÔASCII·½Ê½´ò¿ªÎļþ£¨Ä¬ÈÏ£©¡£
1£ºÒÔUnicode·½Ê½´ò¿ªÎļþ¡£
 
Ïà¹ØÎĵµ£º
CURSOR
==================================
l SQL ÓαêCURSORµÄʹÓÃ
ʹÓÃÆðÀ´ºÜ¼òµ¥£¬Ïȶ¨Ò壬Ȼºó¸³¸öÖµ£¬´ò¿ª£¬Í¨¹ýWhile Loop Ò»¸öÒ»¸ö¶ÁÏÂÈ¥£¬×îºó¹Ø±Õ£¬ÊÍ·ÅÄÚ´æ¡£»ù±¾Ì×·ÈçÏ£º
DECLARE MyCursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪµ¥´¿ÏòÇ°µÄÓαꡣÈç¹ûÏëҪǰºóÌøÀ´ÌøÈ¥µÄ£¬Ð´³ÉScroll Cursor¼ ......
1.´´½¨Êý¾Ý¿â
--exec xp_cmdshell 'mkdir d:\project'--µ÷ÓÃDOSÃüÁî´´½¨Îļþ¼Ð£¬Ê¹Óô˾äÐèÒªÆô¶¯SQLµÄÍâΧ¹¤¾ß
if exists(select * from sysdatabases where name='Êý¾Ý¿âÃû')
drop database Êý¾Ý¿âÃû
set nocount on ......
--SQL¸ß¼¶³ÌÐòÉè¼Æ£º×Ó²éѯ
use AdventureWorks
GO
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL;
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN ......
http://blog.csdn.net/fenglibing/archive/2007/10/24/1841537.aspx
1¡¢½«Ò»¸ö±íÖеÄÄÚÈÝ¿½±´µ½ÁíÍâÒ»¸ö±íÖÐ
insert into testT1(a1,b1,c1) select a,b,c from test;
insert into testT select * from test; (Ç°ÌáÊÇ兩個±íµÄ結構ÍêÈ«Ïàͬ)
insert into notebook(id,title,content)
se ......