½«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ÍâΧӦÓÃÅäÖÃÆ÷£¬Ñ¡
Ïà¹ØÎĵµ£º
×î¼òµ¥µÄSQL ServerÊý¾Ý¿â´æ´¢¹ý³Ì·ÖÒ³
·¢²¼Ê±¼ä£º2008.07.02 05:11 À´Ô´£ºÈüµÏÍø ×÷ÕߣºAlizze
¡¾ÈüµÏÍø£IT¼¼Êõ±¨µÀ¡¿×î¼òµ¥µÄSQL ServerÊý¾Ý¿â´æ´¢¹ý³Ì·ÖÒ³:
1.Ö»ÐèÒªÌṩSqlÓï¾äºÍÿҳµÄ¼Ç¼Êý,Ò³Êý¾Í¿ÉÒÔÁË
2,Ëٶȳ¬¿ìÓ´,100W¼Ç¼1~3Ãë¾Í·Ö³öÀ´ÁË
......
ÌâÄ¿£ºÓÐAºÍBÁ©¸ö±í£¬¶¼¶¨ÒåÏàͬµÄÖ÷¼ü£¬Ð´³öSQLÓï¾äÕÒ³öA±íÖÐÓжøB±íÖÐûÓеļǼ£¬È»ºó°ÑûÓеļǼ²åÈëµ½B±íÖС£
SQLÓï¾äÈçÏ£º
CREATE TABLE A_MINUS_B AS ---´´½¨Ò»¸ö²î¼¯±íA_MINUS_B ,×¢ÒâSELECT Óï¾äÒ»¶¨ÒªA±íÔÚÇ°¡ ......
1.ʹÓÃCÓïÑÔÀ´²Ù×÷SQL SERVERÊý¾Ý¿â,²ÉÓÃODBC¿ª·ÅʽÊý¾Ý¿âÁ¬½Ó½øÐÐÊý¾ÝµÄÌí¼Ó,ÐÞ¸Ä,ɾ³ý,²éѯµÈ²Ù×÷¡£
step1:Æô¶¯SQLSERVER·þÎñ,ÀýÈç:HNHJ,¿ªÊ¼²Ëµ¥ ->ÔËÐÐ ->net start mssqlserver
step2:´ò¿ªÆóÒµ¹ÜÀíÆ÷,½¨Á¢Êý¾Ý¿âtest,ÔÚtest¿âÖн¨Á¢test±í(a varchar(200),b varchar(200))
step3:½¨Á¢ÏµÍ³DSN,¿ªÊ¼²Ëµ ......
(1)¶þ½øÖÆÊý¾ÝÀàÐÍ
¡¡¡¡¶þ½øÖÆÊý¾Ý°üÀ¨ Binary¡¢Varbinary ºÍ Image
¡¡¡¡Binary
Êý¾ÝÀàÐͼȿÉÒÔÊǹ̶¨³¤¶ÈµÄ(Binary),Ò²¿ÉÒÔÊDZ䳤¶ÈµÄ¡£
¡¡¡¡Binary[(n)] ÊÇ n λ¹Ì¶¨µÄ¶þ½øÖÆÊý¾Ý¡£ÆäÖУ¬n
µÄÈ¡Öµ·¶Î§ÊÇ´Ó 1 µ½ 8000¡£Æä´æ´¢ñ¿µÄ´óСÊÇ n + 4 ¸ö×Ö½Ú¡£
¡¡¡¡Varbinary[(n)] ÊÇ n
λ±ä³¤¶ÈµÄ¶þ½øÖÆÊý¾Ý¡£ÆäÖУ ......
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 ......