locked
Loading data from text files into SQL SERVER DATABASE RRS feed

  • Question

  • I have a VB0.6 application which is responsible of automatically loading text file to SQL SERVER database everyday , but this loader is too slow in loading the files , is their a way in SQL SERVER  itself to load text files without taking too much time.
    taking into considerations that the size of these files is not fixed and varies between 5K to 1Gb.

    thank you

    Thursday, October 16, 2014 10:10 AM

Answers

  • You can create an SSIS package or BCP to import the file.

    https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&es_th=1&ie=UTF-8#q=load%20text%20file%20using%20ssis

    https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&es_th=1&ie=UTF-8#q=import%20text%20file%20using%20bcp

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Thursday, October 16, 2014 10:21 AM
  • From VB6 there is not much alternative than to send row by row which indeed is slow.

    But you could write a C# application which uses a table-valued parameter to load the data, and this could be streamed so that the client never has the hold the entire file. I have an example of this in this article on my web site:
    http://www.sommarskog.se/arrays-in-sql-2008.html

    You can also use the SqlBulkCopy class, but I don't have any example of this myself.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 16, 2014 10:10 PM

All replies

  • You can create an SSIS package or BCP to import the file.

    https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&es_th=1&ie=UTF-8#q=load%20text%20file%20using%20ssis

    https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&es_th=1&ie=UTF-8#q=import%20text%20file%20using%20bcp

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Thursday, October 16, 2014 10:21 AM
  • Use

    Bulk insert

    to upload text file.


    Many Thanks & Best Regards, Hua Min

    Thursday, October 16, 2014 10:21 AM
  • From VB6 there is not much alternative than to send row by row which indeed is slow.

    But you could write a C# application which uses a table-valued parameter to load the data, and this could be streamed so that the client never has the hold the entire file. I have an example of this in this article on my web site:
    http://www.sommarskog.se/arrays-in-sql-2008.html

    You can also use the SqlBulkCopy class, but I don't have any example of this myself.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 16, 2014 10:10 PM