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
Job Name CID Product Update Part 1
Step Name (Job outcome)
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
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.