Copying new items from one table to another 1

[fdarkness]

I'm currently running a job in SQL server that copies contents from a daily download to tables in my database. The daily download drops table A then rebuilds it from the backup, which contains the old data, plus the new data that was inserted over the course of the day.

The job that runs on the server queries the data in table A and takes just the new contents to insert them into table B.

The problem is that if the download fails for whatever reason (it's happened), then the job will miss the new data when the download does come in. This is because the way I built the query, it looks for new data from the past day. This query works perfectly *provided* the daily download doesn't fail.

This isn't the best way to do this. I didn't realize that when I wrote it, but when my boss asked me if any of my SQL jobs would mess up if something failed along the way, I realized that this one job relies on the download not failing.

Is there a way to optimize this query so that even if the download is several days late in getting new data, the query will still find new data (ie, stuff that has a startdate several days past)? (I think it means tweaking the WHERE clause, but I'm not quite sure how).

INSERT INTO TableB
     (Number, Name, StartDate, EndDate)
SELECT     TableA.Number, TableA.Name, TableA.StartDate, TableA.EndDate
FROM       TableA
WHERE   TableA.StartDate >= DATEADD(DAY, -2, GETDATE())

 

[Catadmin]

Well, my solution might not be the best one, but it's how I usually do it when I just need content that is actually non-existant in TableB:

INSERT INTO TableB
     (Number, Name, StartDate, EndDate)
SELECT TableA.Number, TableA.Name, TableA.StartDate, TableA.EndDate
FROM TableA a
LEFT OUTER JOIN 
  (Select Distinct Number, StartDate, EndDate 
  FROM TableB with (nolock) ) b
on a.Number = b.Number
WHERE TableA.StartDate <> TableB.StartDate
and TableA.EndDate <> TableB.EndDate

You could also do a subquery in the WHERE clause instead of a join.

...
Where tableA.Number not in 
  (Select Distinct Number 
   FROM Table B)

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"