-- exec dbo.import_data 'C:\Documents and Settings\varunj\My Documents'
ALTER procedure dbo.import_data
@FilePath varchar(2000)
--@filename varchar(2000)
as
declare
@sql nvarchar(50),
@Bank_code numeric(35),
@Bank_transaction numeric(35),
@Money_trn numeric(35),
@Check_num varchar(25),
@FilePath varchar(2000),
@dtsname varchar(1000),
@cmd varchar(2000)
set @dtsname = 'Package.dtsx'
select @cmd = 'DTExec /F "' + @FilePath + @dtsname + '"'
print @cmd
set @sql = ''
--insert new records
insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )
select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a left join
dbo.import_data2 b
on a.bank_code=b.bank_code
where b.bank_code is null
-- update existing records
update dbo.import_data2
set money_trn = x.Money_trn, Bank_code = x.Bank_code, Bank_transaction = x.Bank_transaction, Check_num = x.Check_num
from
(select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a join
dbo.import_data2 b
on a.bank_code=b.bank_code) x
where import_data2.bank_code = x.bank_code
--clean up import_data1 (temp table)
truncate table dbo.import_data1
exec master..xp_cmdshell @cmd
GO
No comments:
Post a Comment