You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
61 lines
1.5 KiB
61 lines
1.5 KiB
|
|
--
|
|
SELECT * FROM dbo.Dict_ReportItem
|
|
|
|
SELECT top 10 * FROM PEIS.dbo.ExamItem
|
|
|
|
|
|
--
|
|
TRUNCATE TABLE Dict_ReportItem
|
|
SET IDENTITY_INSERT [dbo].[Dict_ReportItem] ON
|
|
|
|
INSERT INTO [Dict_ReportItem]
|
|
([ID], [RptItemName], [SpellCode], [Unit], [RptItemCode] )
|
|
SELECT
|
|
id,
|
|
NAME as RptItemName,
|
|
PYM as SpellCode,
|
|
ValueUnit as Unit,
|
|
LisCode as RptItemCode
|
|
FROM
|
|
PEIS.dbo.ExamItem
|
|
where StopOperDate is null
|
|
|
|
---
|
|
|
|
select DISTINCT [FeeItemId], [ExamItemId], [SortNo] from PEIS.dbo.feeitemexamitem where ExamItemId=1
|
|
-- 更新 FeeItemId 和 SortNo
|
|
UPDATE Dict_ReportItem
|
|
SET FId = CASE
|
|
WHEN Dict_ReportItem.FId IS NULL THEN feeitemexamitem.FeeItemId
|
|
ELSE new_record.FId
|
|
END,
|
|
Seq = feeitemexamitem.SortNo
|
|
FROM Dict_ReportItem
|
|
JOIN PEIS.dbo.feeitemexamitem ON Dict_ReportItem.ID = feeitemexamitem.ExamItemId
|
|
-- 使用 OUTER APPLY 来获取需要复制的记录
|
|
OUTER APPLY (
|
|
SELECT TOP 1 FId
|
|
FROM Dict_ReportItem AS dr
|
|
WHERE Dict_ReportItem.ID = dr.ID AND dr.FId IS NOT NULL
|
|
) AS new_record (FId)
|
|
|
|
|
|
SET IDENTITY_INSERT [dbo].[Dict_ReportItem] OFF
|
|
|
|
--
|
|
|
|
INSERT INTO [Dict_ReportItem]
|
|
([RptItemName], [SpellCode], [Unit], [RptItemCode],fid,seq )
|
|
select DISTINCT
|
|
NAME as RptItemName,
|
|
PYM as SpellCode,
|
|
ValueUnit as Unit,
|
|
LisCode as RptItemCode,
|
|
FeeItemId as fid,
|
|
SortNo as seq
|
|
from
|
|
PEIS.dbo.feeitemexamitem a
|
|
left join PEIS.dbo.examitem b on a.ExamItemId=b.id
|
|
where not EXISTS (select 1 from Dict_ReportItem c where c.id=a.ExamItemId and c.fid=a.FeeItemId)
|
|
ORDER BY fid,seq
|
|
|