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

Posted on 2008-11-07
Last Modified: 2013-12-24
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

Question by:onaled777
    LVL 17

    Accepted Solution

    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.

    Author Comment

    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.


    Author Comment

    sorry...on submission of the form.
    LVL 16

    Assisted Solution

    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.
    LVL 17

    Assisted Solution

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Stored procedure query with if 27 32
    AWS Advice on using WHM/cPanel 1 41
    Incremental load example 2 31
    SQL Encryption question 2 33
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and Log…
    The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: : Click on Too…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now