各位大哥:
    
我有三个表:
1:TemporaryStoreroomTable //临时新增表 动态的创建
2:StoreroomTable//保存所有 临时新增表的数据
(orderid        varchar(50),
  masterid       varchar(50),
  mastername     varchar(100),
  numbers        int,
  times          datetime
  PRIMARY KEY(IncomeStoreroomId,OperateTime),

3:AllStoreroomTable//总数量表
(masterid       varchar(50),
  numbers        int,

和两个存储过程://**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表CREATE procedure ProceCreateTemporaryStoreroomFormTable as
       if  Exists(select 1 from dbo.sysobjects where xtype = 'U' and name = 'TemporaryStoreroomTable')
          begin
          drop  table TemporaryStoreroomTable
          end
     
       create  table    TemporaryIncomeStoreroomFormTable
       ( orderid        varchar(50),
         masterid       varchar(50),
         mastername     varchar(100),
         numbers        int,
         times          datetime
        PRIMARY KEY(IncomeStoreroomId,OperateTime),
      )GO//**********每次需要新增数据时,便创建此表,把需要插入的数据 先保存到此表//如果  TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程CREATE procedure  ProcInsertStoreroomTable  
as
declare   @orderid        varchar(50),
          @masterid       varchar(50),
          @mastername     varchar(100),
          @numbers        int,
          @times          datetime 
declare   @InsertIncomeStoreroom_Cursor  CURSOR    begin
          begin tran
          insert  StoreroomTable(orderid,masterid,mastername,numbers,times)
          
          select   orderid,masterid,mastername,numbers,times
          from     TemporaryStoreroomTable          if       @@error<>0
          begin
          raiserror('操作不成功',16,1)
          rollback tran
          return
          end
          SET     @InsertIncomeStoreroom_Cursor=CURSOR SCROLL DYNAMIC 
          FOR
          SELECT  orderid,masterid,mastername,numbers,times
          FROM   TemporaryStoreroomTable
          
          OPEN    @InsertIncomeStoreroom_Cursor
          FETCH   NEXT  FROM  @InsertIncomeStoreroom_Cursor  INTO  @orderid,@masterid,@mastername,@numbers,@times
          WHILE   @@FETCH_STATUS=0          BEGIN
          
          if     exists(select * from    AllStoreroomTable   where  masterid=@masterid)
          begin
          update    AllStoreroomTable
          set       numbers=numbers+@ numbers
          where     masterid=@masterid
          if        @@error<>0 or @@rowcount<>1
          begin
          raiserror('操作不成功',16,1) 
          rollback tran
          return
          end
          end
          else
          begin
          insert     AllStoreroomTable(masterid,masterid)
          values      (@masterid,@masterid)
          if        @@error<>0 or @@rowcount<>1
          begin
          raiserror('操作不成功',16,1) 
          rollback tran
          return
          end
          end
          end  
          FETCH   NEXT  FROM  @InsertIncomeStoreroom_Cursor  INTO  @orderid,@masterid,@mastername,@numbers,@times
          end          CLOSE   @InsertIncomeStoreroom_Cursor
          DEALLOCATE   @InsertIncomeStoreroom_Cursor
 
         if   @@error=0      
          commit tran
         else
          rollback tran
   endGO//如果  TemporaryStoreroomTable 表的数据需要真正的提交才运行此存储过程
各位大哥,现在的问题是,ProcInsertStoreroomTable 我用到了游标,他们说游标的速度会很慢(我不知道是不是真的),各位大哥,是不是这样啊?还有可以不通过游标,
利用别的方法可以实现吗?
谢谢  各位大哥!

解决方案 »

  1.   

    用什么游标,用触发器,完全可以实现你的功能.游标的确是耗资源,微软也不提倡多用,当然如果数据量少,也没什么.
       既然是提交后才更新,全程事务封装,把所有操作交给SQLSERVER就行了.
      

  2.   

    你可以用ADO的缓存更新,也可以用ClientDataSet或者最简单的用个事物控制一下就可以了  adoconnection1.BeginTrans;
      adoconnection1.CommitTrans;
      adoconnection1.RollbackTrans;