SQLTeam.com | Weblogs | Forums

Insert Records


#1

Hi There,

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?

Thanks for the help.

Best Regards,


#2

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;

#3

Hi There,

Sorry, have been away and only just got back to this.

Thanks for your help.
I'll give it a try and see how it goes.

I appreciate you taking the time to help.

Best Regards,


#4

Hi James,

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.

Hope that makes sense.

Thanks for the help.

Best Regards,