--CREATE TABLE #temptableCFG
--(
-- ID INT IDENTITY(1,1),
-- [EquipmentMaintenanceCFGId] CHAR(12),
-- [EquipmentId] CHAR(12),
-- [MaintenanceItem] NVARCHAR(50),
-- [MaintenancePosition] NVARCHAR(50),
-- [MaintenanceBenchmark] NVARCHAR(500),
-- [MaintenanceCyc] INT,
-- [EarlyWarningDays] INT,
-- [MaintTimes] INT,
-- [LastMaintenanceDate] DATETIME,
-- [NextMaintenanceDate] DATETIME,
-- [RealMaintDate] DATETIME,
-- [MainPlanDuration] INT,
-- [AlarmId] CHAR(12),
-- [Eam_MaintPlanId] CHAR(12),
-- [TypesId] CHAR(12),
-- [UpperLimit] DECIMAL(18, 4),
-- [LowerLimit] DECIMAL(18, 4),
-- [PlanType] CHAR(20),
-- [MaintPeriod] CHAR(20),
-- [PeriodValue] INT,
-- [MaintainItemsId] CHAR(12),
-- [Remark] NVARCHAR(80),
-- [CreateDate] DATETIME,
-- [CreateUserId] CHAR(12)
--);
--INSERT INTO #temptableCFG
--(
-- EquipmentMaintenanceCFGId,
-- EquipmentId,
-- MaintenanceItem,
-- MaintenancePosition,
-- MaintenanceBenchmark,
-- MaintenanceCyc,
-- EarlyWarningDays,
-- MaintTimes,
-- LastMaintenanceDate,
-- NextMaintenanceDate,
-- RealMaintDate,
-- MainPlanDuration,
-- AlarmId,
-- Eam_MaintPlanId,
-- TypesId,
-- UpperLimit,
-- LowerLimit,
-- PlanType,
-- MaintPeriod,
-- PeriodValue,
-- MaintainItemsId,
-- Remark,
-- CreateDate,
-- CreateUserId
--)
--SELECT * FROM dbo.EquipmentMaintenanceCFG
DECLARE @EquipmentMaintenanceCFGId CHAR(12),
@EquipmentId CHAR(12),
@MaintenanceItem NVARCHAR(50),
@MaintenancePosition NVARCHAR(50),
@MaintenanceBenchmark NVARCHAR(500),
@MaintenanceCyc INT,
@EarlyWarningDays INT,
@MaintTimes INT,
@LastMaintenanceDate DATETIME,
@NextMaintenanceDate DATETIME,
@RealMaintDate DATETIME,
@MainPlanDuration INT,
@AlarmId CHAR(12),
@Eam_MaintPlanId CHAR(12),
@TypesId CHAR(12),
@UpperLimit DECIMAL(18, 4),
@LowerLimit DECIMAL(18, 4),
@PlanType CHAR(20),
@MaintPeriod CHAR(20),
@PeriodValue INT,
@MaintainItemsId CHAR(12),
@Remark NVARCHAR(80),
@CreateDate DATETIME,
@CreateUserId CHAR(12)
DECLARE @temptable TABLE
(
[EquipmentMaintenanceCFGId] CHAR(12),
[EquipmentId] CHAR(12),
[MaintenanceItem] NVARCHAR(50),
[MaintenancePosition] NVARCHAR(50),
[MaintenanceBenchmark] NVARCHAR(500),
[MaintenanceCyc] INT,
[EarlyWarningDays] INT,
[MaintTimes] INT,
[LastMaintenanceDate] DATETIME,
[NextMaintenanceDate] DATETIME,
[RealMaintDate] DATETIME,
[MainPlanDuration] INT,
[AlarmId] CHAR(12),
[Eam_MaintPlanId] CHAR(12),
[TypesId] CHAR(12),
[UpperLimit] DECIMAL(18, 4),
[LowerLimit] DECIMAL(18, 4),
[PlanType] CHAR(20),
[MaintPeriod] CHAR(20),
[PeriodValue] INT,
[MaintainItemsId] CHAR(12),
[Remark] NVARCHAR(80),
[CreateDate] DATETIME,
[CreateUserId] CHAR(12)
);
--SELECT * FROM #temptableCFG
DECLARE @i INT=0
DECLARE @j INT=(SELECT MAX(id) FROM #temptableCFG)
while @i<=@j
BEGIN
SELECT
@EquipmentMaintenanceCFGId=EquipmentMaintenanceCFGId,
@EquipmentId=EquipmentId,--
@MaintenanceItem=MaintenanceItem,--
@MaintenancePosition=MaintenancePosition,--
@TypesId=TypesId,--
@MaintainItemsId=MaintainItemsId--
FROM #temptableCFG WHERE id=@i
IF NOT EXISTS(SELECT 1 FROM @temptable
WHERE EquipmentId = @EquipmentId
AND MaintenanceItem = @MaintenanceItem
AND MaintenancePosition = @MaintenancePosition
AND TypesId = @TypesId
AND MaintainItemsId=@MaintainItemsId)
BEGIN
INSERT INTO @temptable
(
EquipmentMaintenanceCFGId,
EquipmentId,
MaintenanceItem,
MaintenancePosition,
MaintenanceBenchmark,
MaintenanceCyc,
EarlyWarningDays,
MaintTimes,
LastMaintenanceDate,
NextMaintenanceDate,
RealMaintDate,
MainPlanDuration,
AlarmId,
Eam_MaintPlanId,
TypesId,
UpperLimit,
LowerLimit,
PlanType,
MaintPeriod,
PeriodValue,
MaintainItemsId,
Remark,
CreateDate,
CreateUserId
)
SELECT EquipmentMaintenanceCFGId,
EquipmentId,
MaintenanceItem,
MaintenancePosition,
MaintenanceBenchmark,
MaintenanceCyc,
EarlyWarningDays,
MaintTimes,
LastMaintenanceDate,
NextMaintenanceDate,
RealMaintDate,
MainPlanDuration,
AlarmId,
Eam_MaintPlanId,
TypesId,
UpperLimit,
LowerLimit,
PlanType,
MaintPeriod,
PeriodValue,
MaintainItemsId,
Remark,
CreateDate,
CreateUserId FROM #temptableCFG WHERE
EquipmentId = @EquipmentId
AND MaintenanceItem = @MaintenanceItem
AND MaintenancePosition = @MaintenancePosition
AND TypesId = @TypesId
AND MaintainItemsId=@MaintainItemsId
END
SET @i=@i+1
END
SELECT * FROM @temptable