ÓÃSQL²éѯ·ÖÎöÆ÷²Ù×÷Excel
ÓÃSQL²éѯ·ÖÎöÆ÷²Ù×÷Excel
http://hi.baidu.com/bilbit/blog/item/adf7a33427af52bed0a2d38e.html
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éѯµÄʾÀý£¬Ëüͨ¹ýÓÃÓÚ Jet µÄ OLE DB Ìṩ³ÌÐò²éѯ Excel µç×Ó±í¸ñ¡£
SELECT *
from OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2¡¢½«ExcelµÄÊý¾Ýµ¼ÈëSQL server :
-- ======================================================
SELECT * into newtable
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ʵÀý:
SELECT * into newtable
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
3¡¢½«SQL SERVERÖвéѯµ½µÄÊý¾Ýµ¼³ÉÒ»¸öExcelÎļþ
-- ======================================================
T-SQL´úÂ룺
EXEC master..xp_cmdshell 'bcp ¿âÃû.dbo.±íÃûout c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
²ÎÊý£ºS ÊÇSQL·þÎñÆ÷Ãû£»UÊÇÓû§£»PÊÇÃÜÂë
˵Ã÷£º»¹¿ÉÒÔµ¼³öÎı¾ÎļþµÈ¶àÖÖ¸ñʽ
ʵÀý:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname from pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
ÔÚVB6ÖÐÓ¦ÓÃADOµ¼³öEXCELÎļþ´úÂ룺
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "ma
Ïà¹ØÎĵµ£º
ÒÔÏÂÊÇ·¢ÔÚ÷×ÓÂÛ̳µÄÌù×Ó£¬×ª·¢¹ýÀ´£¬Ï£Íû¸øÓõÃ×ŵĺüÓѲο¼¡£
ÎҵijÌÐòÒÔǰһֱÊÇÓû¨Éú¿Ç°ó¶¨IPʵÏÖµÄÔ¶³Ì£¬ÓÉÓÚ¿Í»§·þÎñÆ÷ºÍ¿Í»§¶Ë¶¼ÊÇͨ¹ý¿í´øÉÏÍø£¬Ò»°ãµÄ²Ù×÷£¨¿ª½ø»õµ¥¡¢ÏúÊÛµ¥µÈ£©ËÙ¶ÈÒ²»¹²»´í£¬µ«ÊÇÔÚÔ¶³Ì¿Í»§¶ËÐÞ¸ÄÉÌÆ·×ÊÁÏ£¨10000¶àÌõ¼Ç¼£©¡¢²éѯһ¶ÎʱÆÚµÄ½øÏú´æÁ÷Ë®£¨Ò»ÖÜ5000ÌõÒÔÉÏ£©µÈÉæ¼°µ½È¡¼Ç¼Á¿´óµ ......
·ÀÖ¹SQL×¢Èë¹¥»÷µÄ×¢ÒâÊÂÏî
Ò». SQL Injection¼°Æä·À·¶µÄ»ù±¾ÖªÊ¶
¿ÉÄÜ´ó¼Ò¶¼ÖªµÀ£¬SQL×¢ÈëÖ÷ÒªÊÇÀûÓÃ×Ö·ûÐͲÎÊýÊäÈëµÄ¼ì²é©¶´¡£
±ÈÈç˵£¬³ÌÐòÖÐÓÐÕâÑùµÄ²éѯ£º
string sql = "SELECT * from SiteUsers WHERE UserName='" + userName + "'";
ÆäÖеÄuserName²ÎÊýÊÇ´ÓÓû§½çÃæÉÏÊäÈëµÄ¡£
Èç¹ûÊÇÕý³£µÄÊäÈ룬±ÈÈç ......
SQLÓαêÔÀíºÍʹÓ÷½·¨
Êý¾Ý¿â¿ª·¢¹ý³ÌÖУ¬µ±Äã¼ìË÷µÄÊý¾ÝÖ»ÊÇÒ»Ìõ¼Ç¼ʱ£¬ÄãËù±àдµÄÊÂÎñÓï¾ä´úÂëÍùÍùʹÓÃSELECT INSERT Óï¾ä¡£µ«ÊÇÎÒÃdz£³£»áÓöµ½ÕâÑùÇé¿ö£¬¼´´Óijһ½á¹û¼¯ÖÐÖðÒ»µØ¶ÁȡһÌõ¼Ç¼¡£ÄÇôÈçºÎ½â¾öÕâÖÖÎÊÌâÄØ£¿ÓαêΪÎÒÃÇÌṩÁËÒ»ÖÖ¼«ÎªÓÅÐãµÄ½â¾ö·½°¸¡£
1.1 ÓαêºÍÓαêµÄÓŵã
ÔÚÊý¾Ý ......
SQLÐÐתÁеĶ¯Ì¬¹¹Ôì·½·¨
/*¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create t ......
Creating a CLR user define aggregate (part 2). Use multiple columns in the aggregation function
In part 1 we created a nice user defined aggregate. Now we are going to make it more sophisticated and let its value depend on two parameters ShipCountry and ShipShipCity. You might try having two parame ......