insert query quandry

[equus2]

I need some help thinking this out...

I have a staging table that gets loaded with data from a file using ssis. The file is a rolling history file, in that every day's current file not only contains that day's data, but also data for the previous days in the month. So for example, if today is 5/3, the data might look like:

2008-05-01, col1, col2, col3, col5
2008-05-01, col1, col2, col3, col5
2008-05-02, col1, col2, col3, col5
2008-05-03, col1, col2, col3, col5
2008-05-03, col1, col2, col3, col5
2008-05-03, col1, col2, col3, col5

Next, I need to load the current day's data (5/3) from the staging table into a production table... however, I only want data from 5/3 because the data from 5/1 and 5/2 are already in the production table.

There is also a possibility that on 5/3, the powers that be may have added or corrected rows from the previous days and added to the file for 5/3... so it isn't enough to just add rows with the 5/3 date on them, because there might be a new or corrected row for 5/2, say.

My idea is to do some sort of join that checks the complete row value (i.e. all the columns) in the staging table against the rows in the production table, so that IF there is a new or corrected row for 5/2, it will insert or update that row along with all the rows for 5/3.

Thanks much!

 

[SQLSister]

What kind of identifier do you have to match the records on?

If you have a join field you can use a left join to identify new records. You can use a join in the update statment to match records with differnt data if you have some kind of identifier.If you have no identifier, I don't see how an update can be done becasue you would have no way to know if it is a new record or an update of an exisiting record..

"NOTHING is more important in a database than integrity." ESquared

 

[RiverGuy]

That's true. You definitely need a business key. If you don't have one, you could always wipe out the data for the month and reload from the file--but that isn't a good idea if you wind up with a partial text file somehow.

 

[ESquared]

If the new data replaces the old data, then on 5/3:

-remove all the previous data for 5/1 and 5/2.
-insert the new data for 5/3.

 

[equus2]

The thing is, even if I had a business key, it's not useful because I am concerned about changes in fields that are not part of the business key. That is, the business key might be the same but another field is different.

The only way to get around this would be to include all the fields in the business key which isn't practical.

However, could someone provide a simple example how to do the join on the business key to make this work?

Thanks much

 

[ESquared]

If the new data coming through is definitively correct for the whole month, then the only sensible course of action is to completely replace the month's production data with the new data.