x

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.
more ▼

asked May 20, 2011 at 01:52 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
If a file is missing, how is it in the collection of files to loop around?
more ▼

answered May 20, 2011 at 02:09 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

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?
May 20, 2011 at 02:11 AM Mrs_Fatherjack
so the code runs for every 'possible' file, and you want to skip those that aren't there.....
May 20, 2011 at 02:13 AM Kev Riley ♦♦
That's it.
May 20, 2011 at 02:15 AM Mrs_Fatherjack
2 loops, one outer that controls whether the file is there, and one inner to process (urgh I'm suggesting loops!!!)
May 20, 2011 at 02:16 AM Kev Riley ♦♦
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.
May 20, 2011 at 02:24 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x734
x24
x7

asked: May 20, 2011 at 01:52 AM

Seen: 1756 times

Last Updated: May 20, 2011 at 01:56 AM