In need of your help again please.
I am trying to repair a database where there are records missing from a table.
I am getting the missing records field values from another table but I need the values to be unique as I cannot add a record that the primary field has already been added. This is the code I am using:
Set Identity_Insert [Tbl_Schedule] On
Insert into Tbl_Schedule(ScheduleID,AssetID,ScheduledDate)
select DISTINCT ScheduleID,AssetID,ScheduledDate from Tbl_WorkOrder
where YEAR(Tbl_WorkOrder.ScheduledDate) = '2017' AND Tbl_WorkOrder.ScheduleID not in(select Tbl_Schedule.ScheduleID from Tbl_Schedule)
Set Identity_Insert [Tbl_Schedule] Off
I need Tbl_WorkOrder.ScheduleID to be unique. At the moment the first select statement is coming back with duplicate scheduleId's
How can I get unique scheduleId's for my first select statement?
Do you care which of the multiple rows that exists for a given scheduleId gets inserted into the table? If you do, change the ORDER BY in the query below.
SET IDENTITY_INSERT [Tbl_Schedule] ON;
;WITH cte AS
(
SELECT
ScheduleID ,
AssetID ,
ScheduledDate,
ROW_NUMBER() OVER(PARTITION BY SchedulID ORDER BY AssetId, ScheduledDate DESC) AS RN
FROM Tbl_WorkOrder AS tw
WHERE
tw.ScheduledDate >= '20170101'
AND tw.ScheduledDate < '20180101'
AND NOT EXISTS
(
SELECT *
FROM Tbl_Schedule AS ts
WHERE
ts.ScheduleID = tw.ScheduleId
)
)
INSERT INTO Tbl_Schedule
( ScheduleID ,
AssetID ,
ScheduledDate
)
SELECT
ScheduleID ,
AssetID ,
ScheduledDate
FROM
cte
WHERE
RN = 1;
SET IDENTITY_INSERT [Tbl_Schedule] OFF;
Your code worked great but another spanner has been thrown into the mix.
I need to get another value from another table.
The other table is called Tbl_Task and the matched fields are Tbl_WorkOrder.ScheduleId and Tbl_Task.ScheduleId and I want to have Tbl_Task.Frequency in the first select statement so I can insert that into Tbl_Schedule along with the other fields.