sql 复制同一张表 数据问题

Question

alter proc proc1 
    @ids varchar(1000) -- such as '1,2,3' 
    ,@types varchar(8000) -- such as '123,456,789' 
as 
begin 
    declare @tb table(id int, cVouchSType varchar(8),cCancelNo varchar(40)) 
    declare @index_1 int, @index_2 int 
    select @ids=@ids+',', @types=@types+',' 
    select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    while @index_1>0 
    begin 
        insert @tb select substring(@ids,1, @index_1-1),substring(@types,1, @index_2-1) 
        select @ids=stuff(@ids,1,@index_1,''),@types=stuff(@types,1,@index_2,'') 
        select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    end   --  if [Auto_ID] is an identity column 
    insert  into  [Ap_Detail]     select  [iPeriod],[cVouchType],b.[cVouchSType],[cVouchID],[dVouchDate] 
      ,[dRegDate] ,[cDwCode] ,[cDeptCode] ,[cPerson] 
      ,[cInvCode] ,[iBVid],[cCode],[cItem_Class] 
      ,[cItemCode],[csign],[isignseq],[ino_id] 
      ,[cDigest],[iPrice],[cexch_name],[iExchRate] 
      ,[iDAmount],[iCAmount] ,[iDAmount_f] ,[iCAmount_f] 
      ,[iDAmount_s] ,[iCAmount_s],[cOrderNo] ,[cSSCode] 
      ,[cPayCode] ,[cProcStyle] 
      ,[cCancelNo] = left([cCancelNo],4) + right('000000000000'+ltrim(cast(right([cCancelNo],13) as bigint)+1),13) --BZAR0000000000016
      ,[cPZid] ,[bPrePay],[iFlag],[cCoVouchType]  ,[cCoVouchID] 
      ,[cFlag] ,[cDefine1],[cDefine2]  ,[cDefine3] ,[cDefine4] 
      ,[cDefine5],[cDefine6] ,[cDefine7]  ,[cDefine8],[cDefine9] 
      ,[cDefine10] ,[iClosesID] ,[iCoClosesID] ,[cDefine11],[cDefine12] 
      ,[cDefine13] ,[cDefine14] ,[cDefine15] ,[cDefine16] 
    from  [Ap_Detail] a join @tb b on a.[Auto_ID]=b.id 
end 
go 

Answer 1

alter proc proc1 
    @ids varchar(1000) -- such as '1,2,3' 
    ,@types varchar(8000) -- such as '123,456,789' 
as 
begin 
    declare @tb table(nid int identity(1,1), id int, cVouchSType varchar(8),cCancelNo varchar(40)) 
    declare @index_1 int, @index_2 int 
    select @ids=@ids+',', @types=@types+',' 
    select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    while @index_1>0 
    begin 
        insert @tb select substring(@ids,1, @index_1-1),substring(@types,1, @index_2-1) 
        select @ids=stuff(@ids,1,@index_1,''),@types=stuff(@types,1,@index_2,'') 
        select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    end  declare @max int
select @max=right(max([cCancelNo]),13) from [Ap_Detail]  --  if [Auto_ID] is an identity column 
    insert  into  [Ap_Detail] 
    select  [iPeriod],[cVouchType],b.[cVouchSType],[cVouchID],[dVouchDate] 
      ,[dRegDate] ,[cDwCode] ,[cDeptCode] ,[cPerson] 
      ,[cInvCode] ,[iBVid],[cCode],[cItem_Class] 
      ,[cItemCode],[csign],[isignseq],[ino_id] 
      ,[cDigest],[iPrice],[cexch_name],[iExchRate] 
      ,[iDAmount],[iCAmount] ,[iDAmount_f] ,[iCAmount_f] 
      ,[iDAmount_s] ,[iCAmount_s],[cOrderNo] ,[cSSCode] 
      ,[cPayCode] ,[cProcStyle] 
      ,[cCancelNo] = left([cCancelNo],4) + right('000000000000'+ltrim(@max+b.nid),13) 
      ,[cPZid] ,[bPrePay],[iFlag],[cCoVouchType]  ,[cCoVouchID] 
      ,[cFlag] ,[cDefine1],[cDefine2]  ,[cDefine3] ,[cDefine4] 
      ,[cDefine5],[cDefine6] ,[cDefine7]  ,[cDefine8],[cDefine9] 
      ,[cDefine10] ,[iClosesID] ,[iCoClosesID] ,[cDefine11],[cDefine12] 
      ,[cDefine13] ,[cDefine14] ,[cDefine15] ,[cDefine16] 
    from  [Ap_Detail] a join @tb b on a.[Auto_ID]=b.id 
end 
go 

Answer 2

alter proc proc1 
    @ids varchar(1000) -- such as '1,2,3' 
    ,@types varchar(8000) -- such as '123,456,789' 
as 
begin 
    declare @tb table(nid int identity(1,1), id int, iDAmount money) 
    declare @index_1 int, @index_2 int 
    select @ids=@ids+',', @types=@types+',' 
    select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    while @index_1>0 
    begin 
        insert @tb select substring(@ids,1, @index_1-1),substring(@types,1, @index_2-1) 
        select @ids=stuff(@ids,1,@index_1,''),@types=stuff(@types,1,@index_2,'') 
        select @index_1=charindex(',',@ids), @index_2=charindex(',',@types) 
    end  declare @max bigint
select @max=right(max([cCancelNo]),13) from [Ap_Detail]  --  if [Auto_ID] is an identity column 
    insert  into  [Ap_Detail] 
    select  [iPeriod],[cVouchType],[cVouchSType],[cVouchID],[dVouchDate] 
      ,[dRegDate] ,[cDwCode] ,[cDeptCode] ,[cPerson] 
      ,[cInvCode] ,[iBVid],[cCode],[cItem_Class] 
      ,[cItemCode],[csign],[isignseq],[ino_id] 
      ,[cDigest],[iPrice],[cexch_name],[iExchRate] 
      ,b.[iDAmount]
  ,[iCAmount] ,[iDAmount_f] ,[iCAmount_f] 
      ,[iDAmount_s] ,[iCAmount_s],[cOrderNo] ,[cSSCode] 
      ,[cPayCode] ,[cProcStyle] 
      ,[cCancelNo] = left([cCancelNo],4) + right('000000000000'+ltrim(@max+b.nid),13) 
      ,[cPZid] ,[bPrePay],[iFlag],[cCoVouchType]  ,[cCoVouchID] 
      ,[cFlag] ,[cDefine1],[cDefine2]  ,[cDefine3] ,[cDefine4] 
      ,[cDefine5],[cDefine6] ,[cDefine7]  ,[cDefine8],[cDefine9] 
      ,[cDefine10] ,[iClosesID] ,[iCoClosesID] ,[cDefine11],[cDefine12] 
      ,[cDefine13] ,[cDefine14] ,[cDefine15] ,[cDefine16] 
    from  [Ap_Detail] a join @tb b on a.[Auto_ID]=b.id 
end 
go