Professional Web Applications Themes

BULK INSERT - Coldfusion - Getting Started

I need to do an insert of 68,000 records from Excel file. This will be done once a week. I don't have permission to use BULK INSERT (SQL Transact) with our ISP. Is there another way to easily and QUICKLY insert these records?...

  1. #1

    Default BULK INSERT

    I need to do an insert of 68,000 records from Excel file. This will be done
    once a week.

    I don't have permission to use BULK INSERT (SQL Transact) with our ISP.

    Is there another way to easily and QUICKLY insert these records?

    sampsas23 Guest

  2. #2

    Default Re: BULK INSERT

    You can upload the Excel file to Coldfusion and do an insert from there. If
    you have access to the CF Administrator, you
    could set this up as a scheduled task to run once a week.

    Here's a link for a custom tag that uploads an Excel file and puts it into a
    query. From there, you could loop through the
    query and insert it into your database:

    http://www.emerle.net/programming/display.cfm/t/cfx_excel2query

    Hope this helps

    James74 Guest

  3. #3

    Default Re: BULK INSERT

    James,

    That would be a good idea, but there is a problem. The ISP won't install custom tags.

    Any other ideas?
    sampsas23 Guest

  4. #4

    Default Re: BULK INSERT

    Save it as a CSV and write a routine to loop through the data and insert it.
    Stressed_Simon Guest

  5. #5

    Default Re: BULK INSERT

    I have done that, but the page times out before it is finished. Eventhough the
    query finishes at a later time, the CF page reports "timeout". I set the
    timeout to the maximum limit.

    I need to find a way to retrieve the data easily and speedily

    sampsas23 Guest

  6. #6

    Default Re: BULK INSERT

    Well if that is the case then you are in trouble. The only way is to split the file into smaller parts.

    Unfotunately, if you save money on hosting you have to live with the limitations.
    Stressed_Simon Guest

  7. #7

    Default Re: BULK INSERT

    Actually I did have a notion but it depends what database you are using?
    Stressed_Simon Guest

  8. #8

    Default Re: BULK INSERT

    The data is from Access and the import is to SQL Server.

    Any ideas would be appreciated.
    sampsas23 Guest

  9. #9

    Default Re: BULK INSERT

    On you page timing out. You might try using the <cfflush> tag after every few
    turns of the loop. It keeps the browser session open and avoids the timeout.
    (just don't put it inside of a <table> tag, as IE won't accept the limited
    content until the <table> tag is closed (</table>)

    Just an idea.

    coderWil Guest

  10. #10

    Default Re: BULK INSERT

    While you can't use bulk insert can you

    use a DTS package ?
    use a stored procedure ?

    either of these can perform what you want.

    Ken
    The Guest

  11. #11

    Default Re: BULK INSERT

    Thanks all for your replies!

    Unfortunately the ISP will only allow SQL datasources so trying to get them to
    use Access database won't work.

    Using CFFLUSH and/or a stored procedure will probably be the way I will go.

    Thanks again!



    sampsas23 Guest

Similar Threads

  1. Bulk eMailing
    By Whizzzper in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: December 4th, 09:23 AM
  2. bulk loading
    By John Miller in forum Photography
    Replies: 13
    Last Post: July 16th, 09:18 PM
  3. BULK INSERT exercises from exam 70-229 training kit don't work
    By gene in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:00 PM
  4. Eliminate logging while doing Bulk Insert
    By RS in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 10th, 03:12 PM
  5. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139