Insert from Flat file only once

[timroop]

We have a flat file that contains payroll information. I know how to import the file into a table using several methods. I can set this up so that a paytech can click a 'magic button' and fire off the import. No problem there.

What I need to do is to setup the system so that the data is only imported once for any given file. For example: The paytech gets a file in the morning and runs the import. The client comes back and reports that there were issues with the file they sent so they send a new file for the day. When the tech runs the import again I need the previously 'bad' import to be removed. I could simply make a datestamp and remove any with the same before I import but...here's the kicker...the client could send a supplemental payroll later in the day with more valid information. In that instance I need the previous data to be kept and the new information added.

I just can't seem to put my mind around a solution. Any suggestions are appreciated.

Thanks.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[gmmastros]

Why not use a DateTime stamp?

Allow the paytech to 'undo' any import based on Date and Time.

Ex:

paytech imports a file on Sept 5, 2008 8:03 AM
paytech later "undo's" the import.
paytech imports a new file on Sept 5, 2008 10:20 AM
paytech imports a supplement on Sept 5, 2008 10:35 AM

Of course, the only trick here is that all rows in the table will need to have the exact same DateTime stamp, but that should be easy enough to accomplish.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MDXer]

what process are you using to get the data from the file into your table?


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[timroop]

MDXer
We hadn't decided on a method at this time. We can use Bulk Copy, DTS, or OpenRowSource. We like the idea of the 'undo' so we may run with that.

But, I'm always looking to learn so what ideas do you have?



Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[MDXer]

If it is SQL 2005 I would use SSIS and what you need could probably be accomplished fairly easily. If not I would use DTS with a staging table then I would build a process to delete and insert based on what you need.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck