ÓÃSQL²éѯ·ÖÎöÆ÷²Ù×ÝExcel¼°µ¼Èëµ¼³öÊý¾Ý
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
ͨ³£µÄ·½·¨ÊÇʹÓÃͼÐνçÃæµÄdts¹¤¾ß£¬µ«·¢¾õÓÐЩʹÓÃÃüÁîÐнçÃæµÄ·½Ê½¸ü¼òµ¥
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$]
-------------------------------------------------------------------------------------
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$]
-------------------------------------------------------------------------------------
½áÂÛ£º ÕâÁ½¸ö¹¦Äܶ¼Ê¹ÓÃÁËopenDatasourceº¯ÊýÀ´·µ»ØÒ»¸öרÓõÄÊý¾Ý¿â·þÎñÆ÷£¬×÷Ϊ4²¿·ÖÃû³ÆµÄµÚÒ»²¿·Ö¡£
OPENDATASOURCE ( provider_name, init_string )
provider_name: ÈçSqloledb£¬ Microsoft.Jet.OLEDB.4.0µÈ
init_string: Á¬½Ó×Ö·û´®£¬Èç'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
ÌṩÁË·þÎñÆ÷Ãû£¬¶ÔExcelÀ´Ëµ¾ÍÊÇÎļþÃû
Óû§ID£¬ÃÜÂë
Extended properties£º ÎÒÊÔÁËÌîExcel 5.0 »ò8.0¶¼¿ÉÒÔ£¬ÆäËû²»ÐÐ
×îºóµÄ[Sheet1$]ÊÇÒ³Ãû£¬ÕâÀïµ±×ö±íÃûÓã¬×¢ÒâÒ»¶¨Òª¼ÓÉÏ$
ÁíÍâÒ»¸öÀý×Ó£¬ÓÃÀ´È¡ÆäËûsql server·þÎñÆ÷ÉϵÄÊý¾Ý£º
SELECT *
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
¹þ¹þ£¬ÑÛÊì¶àÁ˰ɣ¬Ò»ÑùµÄ£¡
µ«ÊÇÕâ¸öÓ÷¨Ö»ÓÃÓÚad hoc£¬ ¶ÔÓÚ´óÁ¿µÄ²Ù×÷»¹ÊÇÓÃlinked server±È½ÏÕý¹æ£¬ linked serverÕâô¼Ó£º
EXEC sp_addlinkedserver 'Excel',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\book1.xls',
NULL,
'Excel 5.0'
GO
¼ÓºÃºó£¬Õâô²éѯ£¨»¹ÊÇÐèÒª$£©£º
select * from excel...Events$
3¡¢½«SQL SERVERÖвéѯµ½µÄÊý¾Ýµ¼³ÉÒ»¸öExcelÎļþ
-- ======================================================
T-SQL´úÂ룺
EXEC master..xp_cmdshell 'bcp
Ïà¹ØÎĵµ£º
1. еIJúÆ·°æ±¾
³ýSQL Server 2005±ê×¼°æºÍÆóÒµ°æÖ®Í⣬²úÆ·ÏßÖл¹¼ÓÈëÁËSQL Server 2005¼ò»¯°æ£¨SQL Server 2005 Express£©ºÍ¹¤×÷×é°æ£¨SQL Server 2005 Workgroup£©Á½¸öа汾¡£
SQL Server 2005 Express——Æä×÷ÓÃÊÇ´úÌæÎ¢ÈíSQL Server×ÀÃæÒýÇæ£¨Microsoft SQL Server Desktop Engine£¬MSDE£©¡£SQL Serve ......
±¾ÖÜ
select * from tb where datediff(week , ʱ¼ä×Ö¶Î ,getdate()) = 0
ÉÏÖÜ
select * from tb where datediff(week , ʱ¼ä×Ö¶Î ,getdate()) = 1
ÏÂÖÜ
select * from tb where datediff(week , ʱ¼ä×Ö¶Î ,getdate()) = -1
------------------------------------------------------------------------------------ ......
PowerDesignerÓëSQL ServerÏàÁ¬
ÒÔǰÌý˵¹ýPowerDesigner¿ÉÒÔºÍÊý¾Ý¿âÁ¬½Ó£¬¸ù¾ÝÔÚPowerDesigner´´½¨µÄÊý¾ÝÄ£ÐÍ´´½¨±í¡¢´¥·¢Æ÷¡¢´æ´¢¹ý³Ìµ½Êý¾Ý¿âÖС£Ò²¿ÉÒÔ½«ÒÑÓеÄÊý¾Ý¿âµ¼³öµ½PowerDesignerÖÐΪÊý¾ÝÄ£ÐÍ¡£½ñÌì×öÁËһϲâÊÔ£¬·¢ÏÖȷʵºÜ¼òµ¥£¬ÏÖÔÚ²Ù×÷²½ÖèÓë´ó¼Ò·ÖÏí£º
0¡¢×¼±¸¹¤×÷
ÏÈÔÚSQL ServerÖд´½¨Ò»Êý¾Ý¿â£¬ ......
11.3µ÷ÓųÌÐò/¼õÇá¸ºÔØ
Èç¹ûÆÚÍû¼õÇáÊý¾Ý¿â·þÎñÆ÷ÕûÌåµÄCPU»òÕßI/O¸ºÔØ£¬¿Éͨ¹ýÒÔϲ½ÖèÈ·¶¨×ÊÔ´Ãܼ¯Ð͵ÄSQLÓï¾ä£º
1.Ñ¡ÔñÔÚÓ¦ÓóÌÐòµÄ´¦Àí¸ß·åÆÚ¼ì²â¡£
2.ÔÚÒÔÉÏÖÜÆÚÄÚ£¬ÊÕ¼¯²Ù×÷ϵͳ¼°Oracleͳ¼ÆÐÅÏ¢¡£ÐèÒªÊÕ¼¯µÄOracleͳ¼ÆÐÅÏ¢ÖÁÉÙ°üÀ¨ÎļþµÄI/O£¨Í¨¹ýÊÓͼV$FILESTAT£©£¬ÏµÍ³Í³¼ÆÐÅÏ¢£¨V$SYSSTAT£©£¬ÒÔ¼°SQLͳ¼ÆÐÅÏ¢£ ......
Èç¹ûSQL Server³ÌÐòÔ±Ï뽫±í´ïʽ´ÓÒ»ÖÖ»»ÎªÁíÒ»ÖÖ£¬Ëû¿ÉÒÔ´ÓSQL Server 7ºÍ2000ÖÐ×Ô´øµÄÁ½ÖÖ¹¦ÄÜÖÐ×ö³öÑ¡Ôñ¡£ÔÚ´æ´¢¹ý³Ì»òÆäËûÇé¿öÏ£¬ÎÒÃdz£³£ÐèÒª½«Êý¾Ý´ÓdatetimeÐÍת»¯³ÉvarcharÐÍ£»CONVERTºÍCAST¾Í¿ÉÒÔÓÃÓÚÕâÖÖÇé¿ö¡£
¡¡¡¡ÓÉÓÚSQL ServerÌṩÁ½ÖÖ¹¦ÄÜ£¬Òò´ËÓ¦¸ÃÑ¡ÔñÄÄÖÖ¹¦ÄÜ»òÓ¦¸ÃÔÚÄÄÖÖÇé¿öÏÂʹÓøù¦ÄܾͺÜÈÝÒ×Èà ......