各位大侠好!我想用参数代替表: DataBoardMainDataPreSave
用参数代替路径 : E:\ShareFolder\看板卡打印.xls试了很多次,都不行,哪位大侠能帮帮忙看看......insert into DataBoardMainDataPreSave
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\ShareFolder\看板卡打印.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]谢谢了!
用参数代替路径 : E:\ShareFolder\看板卡打印.xls试了很多次,都不行,哪位大侠能帮帮忙看看......insert into DataBoardMainDataPreSave
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\ShareFolder\看板卡打印.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]谢谢了!
set @path='xxx'
set @TB='XXX'
set @sql='insert into '+@TB+' SELECT * FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source='''''+@path+''''';User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]'
exec(@sql)
set @ar1='DataBoardMainDataPreSave'
set @ar2='E:\ShareFolder\看板卡打印.xls'
exec('insert into '+@ar1+' SELECT * FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@ar2+'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]''')
EXEC ('
insert into DataBoardMainDataPreSave
SELECT *
FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@s+'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]'')
@str2 nvarchar(30)=N'E:\ShareFolder\看板卡打印.xls'print 'insert into '+@STR1+'
SELECT *
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@str2+'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]'
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\ShareFolder\看板卡打印.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
是取EXcel表的,都是单引号,各位都是双引号,再一个这个是个存储过程,不好意思,我开始没说,大家的通不过。
@tbname sysname, --要导出的表名,注意只能是表名/视图名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='' --文件名,默认为表名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@path+@fname+''',['+@tbname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go