asked
If Break Else Continue and While Loops
I am writing a process that bulk inserts data from a flat file into a new table. To cut a long story short I can't use SSIS/DTS so am writing in code. I have set it up so that all the source files are in a table and have written dynamic sql to pull out the file details, path, name and table to populate. Then using a While Loop it goes through each file and imports the data into the previously stated table. This is working fine, however I now want to add in code to move onto the next file if a file is missing. What is the best method of doing this? I originally wrote it as a If Break else continue script, but the second one I used when there are no files left to import fails as it would appear that you can only use this syntax once inside a while loop. I then considered a Raiserror but can't see how I can use that and get it to continue onto the next file. I'm slightly perplexed any ideas greatfully received.
If a file is missing, how is it in the collection of files to loop around?
2 loops, one outer that controls whether the file is there, and one inner to process (urgh I'm suggesting loops!!!)
It depends - if it works for you then go for it. T-SQL isn't the best for file control, but that's where you are at, and without SSIS/DTS, you need a solution that works and makes sense.
Files will be made available as soon as they become available but the script is due to be run as a scheduled job and for example we may get some files weekly and some monthly and if the scheduled job is run weekly the monthly one may not be available so therefore no processing can take place. Does that make sense?
so the code runs for every 'possible' file, and you want to skip those that aren't there.....
