ALTER PROCEDURE [dbo].[Import_Pan_China_Fx_Recon]
(
@FilePath varchar(4000),
@FileName varchar(4000)
)
AS
BEGIN
set nocount on DECLARE @SqlString VARCHAR(5000)
--游标实例 利用游标循环表
DECLARE @errorr int
DECLARE @temp varchar(50) --临时变量,用来保存游标值
set @errorr=0
BEGIN TRANSACTION Tran_MSG --申明事务
--申明游标
DECLARE order_cursor CURSOR FOR
select sheetname from dbo.[f_sheetname] (''+@FilePath+'\'+@FileName+'' ) where sheetname like '%wb'
--打开游标
open order_cursor WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
FETCH NEXT FROM order_cursor INTO @temp
--执行sql操作
SET @SqlString ='
insert into [S_Pan_China_Fx_Recon] (
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[RECON]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT] )
select
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[check]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT]
FROM
openrowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@FilePath+'\'+@FileName+''',['''+@temp+'$''])' print @SqlString
EXEC (@SqlString) set @errorr=@errorr+@@error --记录每次运行sql后 是否正确 0正确 21 end
print @errorr
if @errorr<>0
ROLLBACK TRANSACTION Tran_MSG --回滚
else
COMMIT TRANSACTION Tran_MSG --提交
end
CLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
set nocount off
END高手帮忙...
(
@FilePath varchar(4000),
@FileName varchar(4000)
)
AS
BEGIN
set nocount on DECLARE @SqlString VARCHAR(5000)
--游标实例 利用游标循环表
DECLARE @errorr int
DECLARE @temp varchar(50) --临时变量,用来保存游标值
set @errorr=0
BEGIN TRANSACTION Tran_MSG --申明事务
--申明游标
DECLARE order_cursor CURSOR FOR
select sheetname from dbo.[f_sheetname] (''+@FilePath+'\'+@FileName+'' ) where sheetname like '%wb'
--打开游标
open order_cursor WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
FETCH NEXT FROM order_cursor INTO @temp
--执行sql操作
SET @SqlString ='
insert into [S_Pan_China_Fx_Recon] (
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[RECON]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT] )
select
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[check]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT]
FROM
openrowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@FilePath+'\'+@FileName+''',['''+@temp+'$''])' print @SqlString
EXEC (@SqlString) set @errorr=@errorr+@@error --记录每次运行sql后 是否正确 0正确 21 end
print @errorr
if @errorr<>0
ROLLBACK TRANSACTION Tran_MSG --回滚
else
COMMIT TRANSACTION Tran_MSG --提交
end
CLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
set nocount off
END高手帮忙...
ALTER PROCEDURE [dbo].[Import_Pan_China_Fx_Recon]
(
@FilePath varchar(4000),
@FileName varchar(4000)
)
AS
BEGIN
set nocount onDECLARE @SqlString VARCHAR(5000)
--游标实例 利用游标循环表
DECLARE @errorr int
DECLARE @temp varchar(50) --临时变量,用来保存游标值
set @errorr=0
BEGIN TRANSACTION Tran_MSG --申明事务
--申明游标
DECLARE order_cursor CURSOR FOR
select sheetname from dbo.[f_sheetname] (''+@FilePath+'\'+@FileName+'' ) where sheetname like '%wb'
--打开游标
open order_cursor WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
FETCH NEXT FROM order_cursor INTO @temp
--执行sql操作
SET @SqlString ='
insert into [S_Pan_China_Fx_Recon] (
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[RECON]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT] )
select
[EXTREFNO]
,[Ccy]
,[Account No]
,[Entry Date]
,[abs]
,[Amount]
,[check]
,[Cr / Dr]
,[Trn Code]
,[Narration]
,[Maker Id]
,[Checker Id]
,[A/c no to be corrected]
,[REMARK]
,[Channel]
,[Value Date]
,[check]
,[ACTIONS BY WBO CHINA]
,[REMARKS BY GRH]
,[DEPT]
FROM
openrowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@FilePath+'\'+@FileName+''',['''+@temp+'$''])' print @SqlString
EXEC (@SqlString)set @errorr=@errorr+@@error --记录每次运行sql后 是否正确 0正确 21 end
print @errorrCLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
if @errorr<>0
ROLLBACK TRANSACTION Tran_MSG --回滚
else
COMMIT TRANSACTION Tran_MSG --提交
end
set nocount off
END
begin
ROLLBACK TRANSACTION Tran_MSG --回滚
end
else
begin
COMMIT TRANSACTION Tran_MSG --提交
end
end
if @errorr<>0
ROLLBACK TRANSACTION Tran_MSG --回滚
else
COMMIT TRANSACTION Tran_MSG --提交放到最下面去