学步园

 set ANSI_NULLS ON<br /> set QUOTED_IDENTIFIER ON<br /> go</p> <p>ALTER proc [dbo].[pr_xls_to_tb]<br /> @path varchar(200),--EXCEL路径名<br /> @tbName varchar(30),--表名<br /> @stName varchar(30) --excel中要读的SHEET名<br /> as<br /> declare @sql varchar(500),--最后要执行的SQL<br /> @stName_Real varchar(35),--真正的SHEET名<br /> @drop_sql varchar(300) -- 如果表已存在，先删除</p> <p>set @stName_Real = '[' + @stName + '$]'<br /> --set @path = 'C:/Inetpub/wwwroot/CarStock_ExcelWeb/Upload/CarStock/国贸汽车库存表20090630.xls'<br /> --set @tbName = 't32'<br /> --set @stName = '[不良资产$]'<br /> set @sql =<br /> 'SELECT *<br /> into '+ @tbName +'<br /> FROM OpenDataSource(' + char(39)+ 'Microsoft.Jet.OLEDB.4.0' + char(39)+', '<br /> + char(39) +'Data Source=' + @path +';User ID=Admin;Password=;Extended properties=Excel 5.0;' + char(39)+')...'+@stName_Real</p> <p>set @drop_sql = '<br /> if exists(select * from sysobjects where name = ' + char(39) +@tbName + char(39)+')<br /> begin<br /> drop table '+@tbName+'<br /> end '<br /> --print @drop_sql<br /> exec (@drop_sql)--先删除表<br /> exec (@sql)--再创建表</p> <p>/*<br /> exec sp_configure 'show advanced options',1<br /> reconfigure<br /> exec sp_configure 'Ad Hoc Distributed Queries',1<br /> reconfigure<br /> */<br />

调用:

exec pr_xls_to_tb 'C:/Tools/预算数据/ys_200908.xls' ,'ys_200908_new' ,'Source'