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

SQLµ¼Èëµ¼³öExcelÊý¾Ý

 --´ÓExcelÎļþÖÐ,µ¼ÈëÊý¾Ýµ½SQLÊý¾Ý¿âÖÐ,ºÜ¼òµ¥,Ö±½ÓÓÃÏÂÃæµÄÓï¾ä:
/*===================================================================*/
--Èç¹û½ÓÊÜÊý¾Ýµ¼ÈëµÄ±íÒѾ­´æÔÚ
insert into ±í select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
--Èç¹ûµ¼ÈëÊý¾Ý²¢Éú³É±í
select * into ±í from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
/*===================================================================*/
--Èç¹û´ÓSQLÊý¾Ý¿âÖÐ,µ¼³öÊý¾Ýµ½Excel,Èç¹ûExcelÎļþÒѾ­´æÔÚ,¶øÇÒÒѾ­°´ÕÕÒª½ÓÊÕµÄÊý¾Ý´´½¨ºÃ±íÍ·,¾Í¿ÉÒÔ¼òµ¥µÄÓÃ:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from ±í
--Èç¹ûExcelÎļþ²»´æÔÚ,Ò²¿ÉÒÔÓÃBCPÀ´µ¼³ÉÀàExcelµÄÎļþ,×¢Òâ´óСд:
--µ¼³ö±íµÄÇé¿ö
EXEC master..xp_cmdshell 'bcp Êý¾Ý¿âÃû.dbo.±íÃû out "c:\test.xls" /c -/S"·þÎñÆ÷Ãû" /U"Óû§Ãû" -P"ÃÜÂë"'
--µ¼³ö²éѯµÄÇé¿ö
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname from pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"·þÎñÆ÷Ãû" /U"Óû§Ãû" -P"ÃÜÂë"'
/*--˵Ã÷:
c:\test.xls   Ϊµ¼Èë/µ¼³öµÄExcelÎļþÃû.
sheet1$       ΪExcelÎļþµÄ¹¤×÷±íÃû,Ò»°ãÒª¼ÓÉÏ$²ÅÄÜÕý³£Ê¹ÓÃ.
--*/
--ÏÂÃæÊǵ¼³öÕæÕýExcelÎļþµÄ·½·¨:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
/*--Êý¾Ýµ¼³öEXCEL
µ¼³ö±íÖеÄÊý¾Ýµ½Excel,°üº¬×Ö¶ÎÃû,ÎļþÎªÕæÕýµÄExcelÎļþ
,Èç¹ûÎļþ²»´æÔÚ,½«×Ô¶¯´´½¨Îļþ
,Èç¹û±í²»´æÔÚ,½«×Ô¶¯´´½¨±í
»ùÓÚͨÓÃÐÔ¿¼ÂÇ,½öÖ§³Öµ¼³ö±ê×¼Êý¾ÝÀàÐÍ
--×Þ½¨ 2003.10(ÒýÓÃÇë±£Áô´ËÐÅÏ¢)--*/
/*--µ÷ÓÃʾÀý
p_exporttb @tbname='µØÇø×ÊÁÏ',@path='c:\',@fname='aa.xls'
--*/
create proc p_exporttb
@tbname sysname,     --Òªµ¼³öµÄ±íÃû
@path nvarchar(1000),    --Îļþ´æ·ÅĿ¼
@fname nvarchar(250)=''   --ÎļþÃû,ĬÈÏΪ±íÃû
as
declare @err int,@src nvarchar(255),@desc nvarchar


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

sql Ð޸ıíÒÔ¼°±í×Ö¶Î

 
ÓÃSQLÓï¾äÌí¼Óɾ³ýÐÞ¸Ä×Ö¶Î
1.Ôö¼Ó×Ö¶Î
     alter table docdsp    add dspcode char(200)
     alter table tbl add meet_group int2
2.ɾ³ý×Ö¶Î
     ALTER TABLE table_NAME DROP COLUMN column_NAME
3.ÐÞ¸Ä×Ö¶ÎÀàÐÍ
&nbs ......

SQL ÁÙʱ±íÓëÁÙʱ±äÁ¿±í

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

SQLÖÐescapeµÄÖ÷ÒªÓÃ;

1.ʹÓà ESCAPE ¹Ø¼ü×Ö¶¨ÒåתÒå·û¡£ ÔÚģʽÖУ¬µ±×ªÒå·ûÖÃÓÚͨÅä·û֮ǰʱ£¬¸ÃͨÅä·û¾Í½âÊÍΪÆÕͨ×Ö·û¡£ÀýÈ磬ҪËÑË÷ÔÚÈÎÒâλÖðüº¬×Ö·û´® 5% µÄ×Ö·û´®£¬ÇëʹÓ㺠WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
2.ESCAPE 'escape_character' ÔÊÐíÔÚ×Ö·û´®ÖÐËÑË÷ͨÅä·û¶ø²»Êǽ«Æä×÷ΪͨÅä·ûʹÓᣠescape_character ÊÇ·ÅÔÚͨÅä·ûǰ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ