Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

½«excelÎļþÖеÄÊý¾Ýµ¼Èëµ¼³öÖÁSQLÊý¾Ý¿âÖÐ

µ¼Èë
Èç¹û±íÒÑ´æÔÚ£¬SQLÓï¾äΪ£º
insert into aa select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
Èç¹û±í²»´æÔÚ£¬SQLÓï¾äΪ£º
SELECT * INTO aa from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
¿ÉÄܻᷢÉúµÄÒì³££º
Èç¹û·¢Éú“Á´½Ó·þÎñÆ÷ "(null)" µÄ OLE DB ·ÃÎÊ½Ó¿Ú "Microsoft.Jet.OLEDB.4.0" ±¨´í¡£Ìṩ³ÌÐòδ¸ø³öÓйشíÎóµÄÈκÎÐÅÏ¢¡£
ÎÞ·¨³õʼ»¯Á´½Ó·þÎñÆ÷ "(null)" µÄ OLE DB ·ÃÎÊ½Ó¿Ú "Microsoft.Jet.OLEDB.4.0" µÄÊý¾ÝÔ´¶ÔÏó¡£”Òì³£¿ÉÄÜÊÇexcelÎļþδ¹Ø±Õ.
Èç¹û·¢Éú“²»Äܽ«Öµ NULL ²åÈëÁÐ 'Grade'£¬±í 'student.dbo.StuGrade'£»Áв»ÔÊÐíÓпÕÖµ¡£INSERT ʧ°Ü¡£
Óï¾äÒÑÖÕÖ¹¡£”Òì³££¬Ôò¿ÉÄÜÊÇexcelÎļþÓëÊý¾Ý¿â±íÖеÄ×ֶβ»Æ¥Åä
ÒÔÉϲÙ×÷µÄÊÇoffice 2003,Èç¹ûÒª²Ù×÷office 2007ÔòÐè²ÉÓÃÈçÏ·½Ê½
Èç¹û±íÒÑ´æÔÚ£¬SQLÓï¾äΪ£º
insert into aa select * from OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
Èç¹û±í²»´æÔÚ£¬SQLÓï¾äΪ£º
SELECT * INTO aa from OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
Èç¹û·¢Éú“Á´½Ó·þÎñÆ÷ "(null)" µÄ OLE DB ·ÃÎÊ½Ó¿Ú "Microsoft.Jet.OLEDB.4.0" ±¨´í¡£Ìṩ³ÌÐòδ¸ø³öÓйشíÎóµÄÈκÎÐÅÏ¢¡£
ÎÞ·¨³õʼ»¯Á´½Ó·þÎñÆ÷ "(null)" µÄ OLE DB ·ÃÎÊ½Ó¿Ú "Microsoft.Jet.OLEDB.4.0" µÄÊý¾ÝÔ´¶ÔÏó¡£”Òì³£¿ÉÄÜÊÇexcelÎļþδ¹Ø±Õ.
Èç¹û·¢Éú“²»Äܽ«Öµ NULL ²åÈëÁÐ 'Grade'£¬±í 'student.dbo.StuGrade'£»Áв»ÔÊÐíÓпÕÖµ¡£INSERT ʧ°Ü¡£
Óï¾äÒÑÖÕÖ¹¡£”Òì³££¬Ôò¿ÉÄÜÊÇexcelÎļþÓëÊý¾Ý¿â±íÖеÄ×ֶβ»Æ¥Åä
ÒÔÉϲÙ×÷µÄÊÇoffice 2003,Èç¹ûÒª²Ù×÷office 2007ÔòÐè²ÉÓÃÈçÏ·½Ê½
ÁíÍ⣬»¹Òª¶ÔһЩ¹¦ÄܽøÐÐÅäÖãº
1¡¢´ò¿ªSQL Server 2005ÍâΧӦÓÃÅäÖÃÆ÷£¬Ñ¡


Ïà¹ØÎĵµ£º

CÓïÑÔÓëSQL SERVERÊý¾Ý¿â

1.ʹÓÃCÓïÑÔÀ´²Ù×÷SQL SERVERÊý¾Ý¿â,²ÉÓÃODBC¿ª·ÅʽÊý¾Ý¿âÁ¬½Ó½øÐÐÊý¾ÝµÄÌí¼Ó,ÐÞ¸Ä,ɾ³ý,²éѯµÈ²Ù×÷¡£
step1:Æô¶¯SQLSERVER·þÎñ,ÀýÈç:HNHJ,¿ªÊ¼²Ëµ¥ ->ÔËÐÐ ->net start mssqlserver
step2:´ò¿ªÆóÒµ¹ÜÀíÆ÷,½¨Á¢Êý¾Ý¿âtest,ÔÚtest¿âÖн¨Á¢test±í(a varchar(200),b varchar(200))
step3:½¨Á¢ÏµÍ³DSN,¿ªÊ¼²Ëµ ......

sqlÁÙʱ±í:SQL ServerÖеÄÁÙʱ±íºÍ±í±äÁ¿

ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£
¡¡¡¡ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£¼ÇµÃÔÚ¸øÒ»¼Ò ......

SQLº¯Êý´óÈ«2

--
¾ÛºÏº¯Êý
use
pubs
go
select

avg
(
distinct
price)  
--
Ëãƽ¾ùÊý
from
titles
where
type
=
'
business
'
go

use
pubs
go
select

max
(ytd_sales)  
--
×î´óÊý
from
titles
go

use
pubs
go
select

min
(ytd_sales)
--
×îСÊý
fr ......

sql overµÄ×÷Óü°Ó÷¨


RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
¿ÉʵÏÖ°´Ö¸¶¨µÄ×ֶηÖ×éÅÅÐò£¬¶ÔÓÚÏàͬ·Ö×é×ֶεĽá¹û¼¯½øÐÐÅÅÐò,
ÆäÖÐPARTITION BY Ϊ·Ö×é×ֶΣ¬ORDER BY Ö¸¶¨ÅÅÐò×Ö¶Î
over²»Äܵ¥¶ÀʹÓã¬ÒªºÍ·ÖÎöº¯Êý£ºrank(),dense_rank(),row_n ......

SQL Server ÓÅ»¯´æ´¢¹ý³ÌµÄÆßÖÖ·½·¨

ÓÅ»¯´æ´¢¹ý³ÌÓкܶàÖÖ·½·¨£¬ÏÂÃæ½éÉÜ×î³£ÓõÄ7ÖÖ¡£
1.ʹÓÃSET NOCOUNT ONÑ¡Ïî
ÎÒÃÇʹÓÃSELECTÓï¾äʱ£¬³ýÁË·µ»Ø¶ÔÓ¦µÄ½á¹û¼¯Í⣬»¹»á·µ»ØÏàÓ¦µÄÓ°ÏìÐÐÊý¡£Ê¹ÓÃSET NOCOUNT ONºó£¬³ýÁËÊý¾Ý¼¯¾Í²»»á·µ»Ø¶îÍâµÄÐÅÏ¢ÁË£¬¼õСÍøÂçÁ÷Á¿¡£
2.ʹÓÃÈ·¶¨µÄSchema
ÔÚʹÓÃ±í£¬´æ´¢¹ý³Ì£¬º¯ÊýµÈµÈʱ£¬×îºÃ¼ÓÉÏÈ·¶¨µÄSchema¡£ÕâÑù¿ÉÒÔÊ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ