求sql【复制同一表记录，但有两个字段需要修改--详情 Btn_oncliek】

Question

insert  into  tbl_name  select  *  from  tbl_name where id in (1,2,3)update tbl_name  
set  cVouchType = case  cVouchType when 1 then 123
                        when 2 then 456
                        when 3 then 789
                  end

Answer 1

insert  into  tbl_name  
select  id,case when id=1 then 123
                when id=2 then 456
                when id=3 then 789
           end as   cVouchType
from  tbl_name 
where id in (1,2,3)

Answer 2

如果写成一句：
insert  into  tbl_name(col1,col2,...,cVouchType)  
select  col1,col2,..., case id when 1 then 123 when 2 then 456 when 3 then 789 end 
from  tbl_name where id in (1,2,3)

Answer 3

id是主键 复制了 1，2，3   以后 形成的数据是  4，5，6  id 是 1，2，3  对应修改 cVouchType内容是 123，456，789    都是前台字符串，是 是个逗号隔开的数组，不确定长度啊

Answer 4

select case id when 1 then 123 when 2 then 456 when 3 then 789 end,col2.... into  tbl_name from tbl_name 
没测试，呵呵

Answer 5

SELECT [Auto_ID]
      ,[iPeriod]
      ,[cVouchType]
      ,[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]
      ,[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]

Answer 6

create 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, cVouchType varchar(8))
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 insert  into  tbl_name(col1,col2,...,cVouchType)  
select  col1,col2,..., T.cVouchType
from  tbl_name join @tb T where tbl_name.id=T.id
end
go

Answer 7

create 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, cVouchType varchar(8))
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]([iPeriod],[cVouchType],...,[cDefine16])  
select  [iPeriod],b.cVouchType,...,[cDefine16] 
from  [Ap_Detail] a join @tb b where a.[Auto_ID]=b.id
end
go

Answer 8

dobear_0922：：：：
(1 行受影响)(1 行受影响)
消息 515，级别 16，状态 2，过程 proc1，第 18 行
不能将值 NULL 插入列 'cVouchID'，表 'Realtour2ERpNew.dbo.Ap_Detail'；列不允许有空值。INSERT 失败。
语句已终止。

Answer 9

 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]
      ,[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
exec proc1 '50,51,61','50,51,61'--------------------------
cCancelNo varchar(40) 这个字段 现在的值是  BZAR0000000000001 位递
    @ids varchar(1000) -- such as '1,2,3'
    ,@types varchar(8000) -- such as '123,456,789'
是外部输入的字符串数组，但是cCancelNo是按照 复制前数据 +1生成的，
比如说 原来是BZAR0000000000001 复制后的数据 对应字段是 BZAR0000000000002
这个怎么搞

Answer 10

--自己改成存储过程应该就可以了吧！
declare @in nvarchar(4000),@out nvarchar(4000)
set @in = '1,2,3'
set @out = '342,34,56'
declare @sql nvarchar(max),@in_temp nvarchar(4000)
set @in_temp=@in
set @sql = 'insert  into  tbl_name  select  id,case '
while (charindex(',',@in)>0) and (charindex(',',@out)>0)
begin
select @sql = @sql+' when id ='+convert(nvarchar(20),left(@in,charindex(',',@in)-1))+' then '+convert(nvarchar(20),left(@out,charindex(',',@out)-1))
set @in = stuff(@in,1,charindex(',',@in),'')
set @out =stuff(@out,1,charindex(',',@out),'')
end
select @sql=@sql+' as cVouchType from tbl_name where id in ('+@in_temp+')'
exec(@sql)

Answer 11

BZAR0000000000002 
BZAR 是写死的 固定得，原来数据id=1，cCancelNo=BZAR0000000000015    把id=1 复制后了，  比如   id=4，cCancelNo=BZAR0000000000016再原来的基础上+1      

Answer 12

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