SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì

SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì
 
·¢²¼Ê±¼ä£º2008.07.11 09:00     À´Ô´£ºÈüµÏÍø    ×÷ÕߣºÐ¡ÇÇ
¡¾ÈüµÏÍø£­IT¼¼Êõ±¨µÀ¡¿SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì:
*--Êý¾Ýµ¼³öEXCEL
 
µ¼³ö±íÖеÄÊý¾Ýµ½Excel,°üº¬×Ö¶ÎÃû,ÎļþΪÕæÕýµÄExcelÎļþ
,Èç¹ûÎļþ²»´æÔÚ,½«×Ô¶¯´´½¨Îļþ
,Èç¹û±í²»´æÔÚ,½«×Ô¶¯´´½¨±í
»ùÓÚͨÓÃÐÔ¿¼ÂÇ,½öÖ§³Öµ¼³ö±ê×¼Êý¾ÝÀàÐÍ
---*/
 
/**//*--µ÷ÓÃʾÀý
 
p_exporttb @tbname='µØÇø×ÊÁÏ',@path='c:',@fname='aa.xls'
--*/
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
 
create proc p_exporttb
@tbname sysname, --Òªµ¼³öµÄ±íÃû,×¢ÒâÖ»ÄÜÊDZíÃû/ÊÓͼÃû
@path nvarchar(1000), --Îļþ´æ·ÅĿ¼
@fname nvarchar(250)='' --ÎļþÃû,ĬÈÏΪ±íÃû
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
 
--²ÎÊý¼ì²â
if isnull(@fname,'')='' set @fname=@tbname+'.xls'
 
--¼ì²éÎļþÊÇ·ñÒѾ­´æÔÚ
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
 
--Êý¾Ý¿â´´½¨Óï¾ä
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+';DATABASE='+@sql+'"'
 
--Á¬½ÓÊý¾Ý¿â
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
 
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
 
--´´½¨±íµÄSQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(