ALTER proc CreateDataSetForExcel(
@tableName varchar(20),--表名
@dir_name varchar(1024)--Excel路径
)
as
begin declare @temp varchar(32)
declare @temp1 varchar(256)
declare @temp2 varchar(32)
declare @temp3 varchar(2048)IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = @tableName and type='U')
BEGIN
exec('DROP Table '+ @tableName)
END
--倒入excel文件
set @temp = '''MSDASQL.1'''
set @temp1 = '''driver=Microsoft Excel Driver (*.xls);HDR=YES;DBQ=' + @dir_name + ' '''
set @temp2 = '''select * from [Sheet1$]'''
set @temp3 = 'SELECT * INTO '+ @tableName +' FROM OPENROWSET(' + @temp + ' ,' + @temp1 + ' , ' + @temp2 + ' ) '
--print @temp3
exec( @temp3 )end谢谢回复!!
執行前顯示一下語句。。
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
然后把显示出来的语句放到查询分析器中执行看是否正确.
go
alter proc CreateDataSetForExcel(
@tableName varchar(20),--表名
@dir_name varchar(1024)--Excel路径
)
as
begin declare @temp nvarchar(32)
declare @temp1 nvarchar(256)
declare @temp2 nvarchar(32)
declare @temp3 nvarchar(2048)IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = @tableName and type='U')
BEGIN
exec('DROP Table '+ @tableName)
END
--倒入excel文件
set @temp = '''Microsoft.Jet.OLEDB.4.0'''
set @temp1 = '''EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;DATABASE=' + @dir_name + ''''
set @temp2 = 'sheet1$'
set @temp3 = 'SELECT * INTO '+ @tableName +' FROM OPENROWSET(' + @temp + ' ,' + @temp1 + ' , ' + @temp2 + ' ) '
--print @temp3
exec( @temp3 )end
改
<2>SELECT * INTO rwe
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0' ,'Excel 8.0;Database=C:\Inetpub\wwwroot\load\20081218135913.xls ' , 'SELECT * FROM [Customers$]')怎么这样不能将Excel导入数据库
错误:OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给出有关错误的任何信息。]。
SELECT * INTO rwe from
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0','Extended Properties=Excel 8.0;Data source=C:\Inetpub\wwwroot\load\20081218135913.xls')...[Filiale1$]