Entering the contents of a remote notepad file via ColdFusion 8 into an SQL Server 2005 database

I have a notepad file with about 50,000 lines.  Each line consists of a six digit number that must be entered into a field in a table in a an SQL Server 2005 database.

I am tempted to simply create an ftp connection to the file, and then process its content line by line, but I suspect that might be a little slow.

Can anyone suggest a more efficient way to do this?

Bear in mind that the technologies being used are:

1) ColdFusion Server 8.0
2) SQL Server 2005

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Use SSIS. I assume the file is a .txt file.
In SQL Server Management Studio, Right click on the destination database and select Tasks, Import Data. This will initiate a wizard to configure you source file and destination table. It is fairly easy if you follow the wizard.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
onaled777Author Commented:
Thank for your response...

But can I call that job from coldFusion.  
Or is it something I will have to manually do in SQL Server Management Studio?

Right now I have the filename that can be passed from the front end and I want the job to run on button upload.

onaled777Author Commented:
sorry...on submission of the form.
If you were wanting to do this with Coldfusion, I'd use CFFTP, CFHTTP or CFFile to get the file contents into a variable.  

Then something like:
<cfset delims = Chr(13) & Chr(10)>  <!--- CRLF --->
<cfset data = ListToArray(yourfile, delims)>      <!--- put it all into an array --->

<cfquery name="addData" datasource="#yourdsn#">
      <cfloop index="i" from="1" to="#ArrayLen(data)#">
            INTO YourTable (columnName)
            VALUES (#data[i]#);

However, a better way might be to use CFLoop and set the file attribute to point to your file (you may have to use FTP to get it onto your server to begin with).  Then loop through the file one line at a time, doing a new query each time.  The above method might crash with 50,000 queries all at the same time, but reading the file line at a time and processing each separately should work better.

But the best way is definitely do it from SQL Server, as mentioned by HoggZilla.
You can save the wizard task to an SSIS pacakge. That package is then defined as a step in an SQL Server Agent Job. Once that job exists it can be called from a SQL Statement using the sp_start_job stored procedure. If the filename will be dynamic then you need some logic in your SSIS package to define the "connectionstring" for the file. Not too difficult if you need this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.