清理Trans表（一）

利用了cursor和while来读取数据，并把数据写入新的表。此次操作并没有更新originalAmount 的值，也没有对空的bank进行赋值

 

declare @trans_id varchar(30);
declare @account_id int;
declare @amount decimal(18,0);
declare @balance decimal(18,0);
declare @originalAmount decimal(18,0);
declare @trans_type varchar(20);
declare @bank varchar(50);
declare trans_cursor CURSOR FOR
select trans_id,bank.dbo.account.account_id,amount,balance,[type],bank
from bank.dbo.account
INNER JOIN bank.dbo.district ON bank.dbo.account.district_id = bank.dbo.district.district_id
INNER JOIN bank.dbo.trans ON bank.dbo.account.account_id = bank.dbo.trans.account_id
open trans_cursor;
fetch next from trans_cursor into @trans_id,@account_id,@amount,@balance,@trans_type,@bank;
while @@fetch_status = 0
 begin
  insert into bank.dbo.trans_filtered values(@trans_id,@account_id,@amount,@balance,0,@trans_type,@bank)
  fetch next from trans_cursor into @trans_id,@account_id,@amount,@balance,@trans_type,@bank;
 end;
close trans_cursor;
deallocate trans_cursor;