/*
**如果提示列名不符的话可能是把EXECL表的隐藏的列导入了. select * from LL 看下LL 表中的列是否有多余的! 重新复制到新的工作薄中去导入就行
*/
create proc [dbo].[X-Execl导入](
@tname varchar(1000) , --要导入的表名
@fn varchar(1000) , --路径
@sheet1 varchar(1000) --execl中表的名称
)
as
declare @s varchar(1000)
declare @ex varchar(1000)
declare @q varchar(1000)
declare @m varchar(1000)
declare @n varchar(1000)
declare @sql varchar(1000)
declare @zd varchar(1000)
declare @zdd varchar(1000)
declare @zfc varchar(1000)
declare @name varchar(1000)
declare @count int--用户记录条数
declare @sl int
declare @stra int
set @count=null
set @sl=1
set @zd= ''
if exists (select * from sysobjects where id = object_id(N'[dbo].LL') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table LL
if exists (select * from sysobjects where id = object_id(N'[dbo].CC') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table CC
--IF Object_id('Tempdb..#LL') IS NOT NULL
--DROP TABLE #LL
--参数检测
if isnull(@fn,'')<>'' set @fn= @fn+'.xls'
if isnull(@sheet1,'')='' set @sheet1='sheet1'
--导入到表中
set @s ='''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 8.0 '''
set @q = 'SELECT top 1 * into LL FROM OpenDataSource ('+@s+')...'+ @sheet1+'$'
exec(@q)
--select name from syscolumns where id = object_id('LL'); --列出表的列名
--select name from syscolumns where id = object_id(''+@tname+'');
IF EXISTS( select name from syscolumns where id = object_id('LL')
and RTRIM(ltrim(name)) not in ( select RTRIM(ltrim(name)) from syscolumns where id = object_id(''+@tname+'')) )
begin
print '列名与表不一致,请核对!或重新复制资料到新EXECL工作薄中进行导入'
end
else
begin
if exists( select name from syscolumns where id = object_id('LL'))
begin
--获取记录条数
select @count =count(name) from (select name from syscolumns where id = object_id('LL')) A
--循环插入记录
while @count+1>@sl
begin
select @name=name
from (select row_number() over(order by name asc) as num,name from syscolumns
where id = object_id('LL')) as T where num=@sl
set @sl=@sl+1
set @zd=@zd+','+@name
end
set @stra=len(@zd)
set @zd=right(@zd,@stra-1)
set @zd=ltrim(rtrim(@zd))
set @zfc= replace(@zd,',',' as varchar(200))+cast( ')
set @zdd= 'select '+@zd+' into CC from '+@tname+' where 1<>1' --防止数据类型不匹配
exec (@zdd)
set @m ='''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 8.0 '''
set @n = 'insert into CC ('+@zd+') select '+@zd+' FROM OpenDataSource ('+@m+')...'+ @sheet1+'$'
exec (@n)
set @zd =' insert into '+@tname+'('+@zd +') select '+@zd+' from CC where cast('+@zfc+' as varchar(200)) is not null'
EXEC (@zd)
print @ex
end
end
-------------
exec [X-Execl导入] 'table','d:\导入表','sheet4'
