Create an automated SQL table rebuild from flat file

I have 2 tables in SQL 2005 Standard edition which are created from a text files daily and I would like to create a stored procedure which can run every run hour automatically to update these tables from the text files which are stored on my local network.

I am happy with the process of creating the stored procedure but it will not run and reports this error

**

Date 02/07/2012 11:31:44

Log Job History (CID Product Update Part 1)

Step ID 0

Server SHE-SQL01

Job Name CID Product Update Part 1

Step Name (Job outcome)

Duration 00:00:01

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

The job failed. The Job was invoked by User AD\administrator. The last step to run was step 1 (Run FUll SSID Update).

**

When the tables are currently created we have to delete the previous content because otherwise it duplicates the entries rather than updating them with new entries and existing content column updates which would be the preferred option.

In summary I require a stored produced which will update 2 tables every hour from text files stored on my network. The tables are a list of products and I would rather update/replace/add existing/new records rather than deleting the existing content but I cannot have duplicate product codes in the tables.

SQL

Project ID: #1731524

About the project