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