Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    So Paulo, Brasil
    Posts
    4

    Question Unanswered: Bulk Insert doubts

    Hello Guys,

    soon soon, ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As Ive read some old post, Ive understand that I have to use BULK INSERT , or else, BCP or DTS. Id like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.

    thanx all

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you look it up in BOL....

    How is the data stored?

    anyway...I almost exclusivley use bcp for Production code


    BULK INSERT usually for quick data analysis..

    and DTS for analysis, if the data is in Excel, Access, whatever...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DTS is the most flexible, because it can go from more or less automated (using the wizard), to very task oriented (using the painter), to micro-managing the transfer (using VBA).

    BCP and BULK INSERT are both tools that expose the current equivalents to the old Bulk Copy API. They are very efficient, but not extremely flexible or friendly.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But what's the most effecient?

    EDIT: And do you not think releasing DTS to a production environment is more painful?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    BCP and BULK INSERT are simply differet front ends to the same code. Performancewise the difference is irrelevant.

    I live and breathe in a replicated environment. One of our machines does virtually all of our DTS jobs against an "interface" server that has little or no user load.

    We use DTS against our OLTP servers, our DW servers, and a number of "friendly" machines that need data. I've never had any real complaints about it.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Location
    So Paulo, Brasil
    Posts
    4

    Environment

    Well thanx for all replies... Talking about what I have... Ill have 30 ASCII files to load as I told you, and approximatly 91292,42 KB for each... Itll run in production environment, to synchronize the ambient for a BI system... Which comand should I use?

    Originally posted by Brett Kaiser
    Did you look it up in BOL....

    How is the data stored?

    anyway...I almost exclusivley use bcp for Production code


    BULK INSERT usually for quick data analysis..

    and DTS for analysis, if the data is in Excel, Access, whatever...

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I suggest DTS is best and reliable tool to import those ASCII text files to the database. And even you can schedule the same package if its ongoing BI requirement.

    As suggested you should follow books online for all the information.
    For DTS specifically keep in touch with http://www.sqldts.com.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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