SQLTeam.com | Weblogs | Forums

Updating Current table with next day's data while keeping the previous data

,
#1

I have a table that I get on the 1st of each month, and it has all the jobs scheduled for the entire month:

SELECT [plannedstartDate]
,[plannedendDate]
,[machine]
,[Job]
,[Qty]
FROM ScheduleJobs
Day 1: The table has all the jobs scheduled for the entire month (Original Table)
Days 2: I get another table that has the entire month of the job scheduled minus day 1 data
Day 3: I get another table that has the entire month of the job scheduled minus the day 1 and day two data
Day 4: I get another table that has the entire month of the job scheduled minus the day 1, day 2, and day 3
And the same pattern goes for the rest of the month, day 5, day 6, until the last day of the month.
I want to write a query that allows me to update the Original table daily to keep only the previous day's data and replace the rest of its data with the subsequent current day's data.

Any suggestion on how to accomplish this will be greatly appreciated.
Thanks.

#2

You cannot do this in one statement. I would use the EXISTS statement to update existing records and add any new records.

EXISTS (Transact-SQL) - SQL Server | Microsoft Docs

With this simple table that you will update only once a day you could also use the merge statement to do it in 1 statement. It keeps the data as it is and it only insert new records and update the other records if needed. However, if you read the documentation it's better to use the exists method.

MERGE (Transact-SQL) - SQL Server | Microsoft Docs

If you provide code to create a sample I can help you with the exists or the merge statement.

#3

How you accomplish this is going to be determined by how you are getting these tables. My guess is that you are not actually getting a table - rather you are getting a file and loading that data into a table.

Can you expand on how you are receiving this data? And what do you mean you want to keep the 'previous' days data? Can you show some examples of what that would look like?

Further - why would you want to get rid of data for the 1st of the month as soon as you load the 3rd of the months data? And then remove the data for the 2nd of the month after loading the file/table on the 4th?

#4

Here is the solution that I was able to figure out in case others have similar needs:

SELECT [plannedstart]

,[plannedend]

,[machine]

,[Job]

,[Qty]

FROM ScheduleJobs

#5

Okay - not sure how that is a solution to your original question. All that shows is a query - but doesn't answer the question you asked.

#6

Assume that the permanent job table is "ScheduleJobs" and the file you receive every day is "ScheduleJobs_new", then:


DECLARE @plannedstartDate_new date
SELECT @plannedstartDate_new = MIN(plannedstartDate)
FROM ScheduleJobs_New

DELETE FROM ScheduleJobs 
WHERE plannedstartDate >= @plannedstartDate_new

INSERT INTO ScheduleJobs
SELECT *
FROM ScheduleJobs_New
1 Like
#7

Thank you, Scott!