0 Replies Latest reply on Dec 17, 2006 5:04 PM by Newsgroup_User

    Bulk insert of a text file on web server into mssql table

    Level 7
      I have a 14mb text file that is being FTP'd down to my hosted web site. I
      need to put the contents of the file into a ms sql table.

      I tried every way to connect to this file remotely (Sql and web server are
      on different boxes) through DTS MSSQL.
      I had no luck and couldnt find any info on google about connecting to the
      remote text file so I have resorted to use coldfusion to handle the bulk
      table insert. I know, I know, Yuck!!

      <cfhttp method="get" textqualifier=" " firstrowasheaders="no" delimiter="|"
      username="xxxxx" password="xxxxxxx" name="test"

      Anyway after getting the files contents like above I now need to do the

      In a normal coldfusion page I would use the code below to loop through but
      how do I loop through the query in a MS SQL stored procedure.? Or how would
      i use the bulk insert command to do this?

      <cfloop query="test">
      <cfquery datasource="#client.DSN#" username="#client.UserName#"
      INSERT INTO products (product, des, unitofmeas, SOH, Price, discount)
      VALUES ('#test.column_1#', '#test.column_2#', '#test.column_3#',
      '#test.column_4#', '#test.column_5#', '#test.column_6#')