/**//*+-------------------------------------- | 存储过程:SP_BulkInsertFromExcel | 功能说明:根据Excel文件导入数据库中的表 | 维护记录: | 调用方式:EXEC SP_BulkInsertFromExcel 'C:鞍山市地方税务局登记信息表.xls','aaaa','组织机构代码,注册号,企业名称,法定代表人,纳税人标识,注册地址,纳税人主管税务机关,主体税种,登记状态','地税登记信息' | 联系方式:Spark.Zou@hotmail.com | 创建日期:2007-05-07 22:26:09.873 | 注意事项: | 版权信息: 邹黎鹏 --------------------------------------+*/ CREATEPROC SP_BulkInsertFromExcel @fnameNVARCHAR(260), @sheenameNvarchar(256), @columnnamevarchar(2000), @TABLENAMEVARCHAR(100) as set nocount on declare@srv_name sysname,@sqlnvarchar(4000),@COLUMNVARCHAR(2000),@IDENTITYNAMEVARCHAR(100),@SQLWherevarchar(2000) SELECT@COLUMN='',@IDENTITYNAME='',@SQLWhere='',@sql='' SELECT@COLUMN=@COLUMN+','+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) AND NAME NOTIN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@TABLENAMEANDCOLUMNPROPERTY( OBJECT_ID(@TABLENAME),COLUMN_NAME,'IsIdentity')=1 ) ORDERBY COLORDER SET@COLUMN=STUFF(@COLUMN,1,1,'') SELECT @SQLWhere=@SQLWhere+' and '+A.NAME+'<>'''' AND ' FROM SYSCOLUMNS A LEFTJOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID WHERE A.ID=OBJECT_ID(@TABLENAME) AND A.ISNULLABLE!=1ANDISNULL(E.TEXT,'')='' SET@SQLWhere=stuff(@SQLWhere,1,1,'') SET@SQLWhere=LEFT(@SQLWhere,LEN(@SQLWhere)-3) if@SQLWhere<>'' begin SET@sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' ) SELECT '+@columnname+' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$]) where 1=1 '+@SQLWhere end else begin SET@sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' ) SELECT '+@columnname+' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$]) ' end Exec(@sql) GO