Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: Bulk insert alternative?

    I have data in an xml format...

    Code:
    <Store1>
      <sale>
        <productid>123</productid>
        <salesperson>bob</salesperson>
        <time>12:00</time>
      </sale>
      <clockedin>
        <salesperson>bob</bob>
        <time>11:45</time>
      </login>
    
       etc....
    
    </Store1>
    My database tables are as follows:

    table1
    id (identity field - primary key)
    storeid (varchar)
    event (varchar)
    time (datetime)

    table 2
    id *
    name (varchar) *
    value (varchar)
    * primary key

    Using the above xml data example, my records would look like this in the tables:

    Table 1
    id: 1
    StoreID: Store1
    Event: sale
    Time: 12:00

    id: 2
    StoreID: Store1
    Event: clockedin
    Time: 11:45

    Table 2
    id: 1
    Name: productid
    Value: 123

    id: 1
    Name: salesperson
    Value: bob

    id: 2
    Name: salesperson
    Value: bob



    Soooo, now I can handle this programatically by reading my xml data and inserting this data into the database as its read by the xml parser. But, what I'm wondering is if there is faster way of doing this. Possibly using some sort of bulk insert?

    The xml data files will at times be massive. For example during the holiday season, there could be millions of records needing to be inserted. However, there will also be days when the data is small.

  2. #2
    Join Date
    Jan 2008
    Posts
    5
    On a side note, I am able to reformat the xml layout if it would be better served to be in a different format.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, note that this is one big honking IF.

    If you can get the data into a flat text file (fixed width columns, CRLF delimted rows) or you can write an XML parser that is significantly more efficient than the one provided by Microsoft that will produce a similar flat file, then probably.

    The BCP.EXE program is orders of magnitude faster than nearly any other way to pump data into SQL Server, and the flat files described above are its format of preference. BCP.EXE can inhale data at speeds that push the theoretical limits of how fast you can get data onto disk using SQL Server.

    -PatP

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there is also XmlBulkLoad:

    http://msdn2.microsoft.com/en-us/library/ms171806.aspx

    I have never used it, but I hear it's pretty fast, comparable with bcp. more of a pain to get working I expect.

    bcp is definitely the way to go if you can get your data into a delimited format.

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    Yeah, I imagine I can write the data to a file.

    If I opt for BCP, would I need to create two unique files, or can I somehow keep the data all in one file? I've used bcp before, but that was long ago and I don't recall its limitations offhand.

    I realize this is the MSSQL forums, but does MySQL have a bcp utility as well?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You would need to create two different files, one for each table in order for BCP.EXE to process them.

    Some versions of MySQL have bulk import capabilities. I'd post the question in the MySQL forum and include your MySQL version information in order to get a more useful answer.

    -PatP

  7. #7
    Join Date
    Jan 2008
    Posts
    5
    Thanks.

    Appreciate the answers.

Posting Permissions

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