22,209
社区成员
发帖
与我相关
我的任务
分享
recorderNO ncd40924ncdrw curStatus ncd40924ncjrw05 procID curNodeID linkRecordID
Z201410220000168 Z200902090000007 2 概预算 wfprocZ141022000015 xmgzrvZ131017000003 Z201410220000007
Z201410220000169 Z201301070000039 2 水土保持 wfprocZ141022000015 xmgzrvZ131017000003 Z201410220000007
Z201410220000170 200804170000012 2 规划 wfprocZ141022000015 xmgzrvZ131017000003 Z201410220000007
Z201410220000171 200804170000012 2 施工 wfprocZ141022000015 xmgzrvZ131017000003 Z201410220000007
Z201410220000172 Z201301070000039 2 勘测 wfprocZ141022000015 xmgzrvZ131017000003 Z201410220000007
Z201410220000173 200804170000012 2 建筑 wfprocZ141022000015 hzyjvvZ131017000001 Z201410220000022
Z201410220000174 200804170000012 2 水土保持 wfprocZ141022000015 hzyjvvZ131017000001 Z201410220000022
Z201410220000175 200808010000003 2 概预算 wfprocZ141022000015 hzyjvvZ131017000001 Z201410220000022
Z201410220000176 200811290000002 2 机电 wfprocZ141022000015 hzyjvvZ131017000001 Z201410220000023
这是原型
insert into ncdbmZ14092400001--表名
select
recorderNO,--主键(类型为varchar)
ncd40924ncdrw02, --字段
curStatus,--字段
ncd40924ncjrw05,--字段
procID,--字段
curNodeID,--字段
linkRecordID --字段
from ncdbmZ14092400001 --表名
where procID='条件' and curNodeID='条件' and linkRecordID ='条件';
这是我要操作的数据
insert into ncdbmZ14092400001--表名
select
‘替换成想插入的数据’,--主键(类型为varchar),主键不能重复
ncd40924ncdrw02, --字段
curStatus,--字段
ncd40924ncjrw05,--字段
procID,--字段
'替换成想插入的数据',--字段,
'替换成想插入的数据' --字段
from ncdbmZ14092400001 --表名
where procID='条件' and curNodeID='条件' and linkRecordID ='条件';
/* 不就是需要按编码规则生成新记录的 recorderNo 而已。
估计 recorderNo 编码规则为: 'Z' + yyyymmdd + 7位序号
*/
DECLARE @prefix varchar(9) -- 今天的编码前缀
DECLARE @serialNo int -- 今天的最大序号
SET @prefix = 'Z' + Convert(varchar(8),GetDate(),112)
SELECT @serialNo = Convert(int,
SubString(ISNULL(MAX(recorderNo),
@prefix + '0000000'
),
10,
7)
)
FROM ncdbmZ14092400001
WHERE recorderNo LIKE @prefix+'%'
;WITH newData AS (
--这是我要操作的数据
select ROW_NUMBER() OVER(ORDER BY recorderNo) rn,
ncd40924ncdrw02,
curStatus,
ncd40924ncjrw05,
procID,
'替换成想插入的数据' curNodeID,
'替换成想插入的数据' linkRecordID
from ncdbmZ14092400001
where procID='条件'
and curNodeID='条件'
and linkRecordID ='条件'
)
insert into ncdbmZ14092400001
SELECT @prefix + Right('0000000' + Convert(varchar(7),
@serialNo+rn),
7),
ncd40924ncdrw02,
curStatus,
ncd40924ncjrw05,
procID,
curNodeID,
linkRecordID
from newData
--Step #1:
;WITH t1 AS
(
SELECT recorderNO,--主键(类型为varchar)
ncd40924ncdrw02, --字段
curStatus,--字段
ncd40924ncjrw05,--字段
procID,--字段
curNodeID,--字段
linkRecordID, --字段
ROW_NUMBER() OVER(ORDER BY recorderNO) 'Flag' --Flag这里列将是唯一的
from ncdbmZ14092400001 --表名
where procID='条件' and curNodeID='条件' and linkRecordID ='条件'
)
--Step #2:
INSERT INTO ncdbmZ14092400001
SELECT Flag, --将Flag列替换recorderNO列
ncd40924ncdrw02, --字段
curStatus,--字段
ncd40924ncjrw05,--字段
procID,--字段
curNodeID,--字段
linkRecordID --字段
FROM t1
--Step #3:
--第三步按需求更新recorderNO的值