• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

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

  • 2
  • 2
3 Solutions
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.
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.

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now