ÓÃ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
Ïà¹ØÎĵµ£º
½ñÌìÏëÁ¬½ÓSQL Server·þÎñÆ÷ʱ·¢ÏÖû·¨Á¬½Ó£¬·¢Ïֶ˿ںŲ»ÊÇĬÈϵĶ˿ںţ¬ÐèÒªÖ¸¶¨¶Ë¿ÚºÅ£¬¸Õ¿ªÊ¼ÒÔΪÊÇIPµØÖ·¼ÓðºÅÔٶ˿ںžÍÐÐÁË£¬½á¹û·¢ÏÖûÓã¬googleÒ»°Ñ·¢ÏÖÔÀ´²»ÊÇðºÅ£¬ÊǶººÅ£¬¹þ¡£±ÈÈ磺127.0.0.1,1451 , ¸ñʽ¾ÍÊÇ£º[IP],[Port] ¡£ Íê±Ï¡£ ......
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ͳ¼ÆÐÅÏ¢£ ......
½ñÌì·¢ÏÖÔÚʹÓÃPL/SQLʱ£¬ÎÞ·¨µÇ½¡£¾¹ýȺÀïÅóÓѵİï棬×îºóÔ²Âú½â¾ö£¬ÏÖÁô¸ö¼Ç¼ÒÔ±ãÒÔºó¿É²é¡£
<!--
/* Font Definitions */
@font-face
{font-family:ËÎÌå;
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-alt:SimSun;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
......