locked
bulk insert best approach RRS feed

  • Question

  • Hi,

    I am using sql server 2008 r2. I am inserting bulk data into a table using OPENXML. I don't see any performance issues currently. But I want to see this in future perspective.

    So I just wanted to know if that is the best approach or is there any other better approach which improves the performance.

    Thanks,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, January 23, 2013 7:28 AM

Answers

  • It's difficult to assist with performance problems you don't yet have.

    But a more straightforward method to pass lots of data from client to server is table-valued parameters, a new feature in SQL 2008. This article on my web site may be of interest:
    http://www.sommarskog.se/arrays-in-sql-2008.html

    If you use OPENXML, because you get XML from somewhere, I think it is better to use XQuery. XQuery generally shreds the XML faster than OPENXML if do it right, and you don't have litter the code with sp_xml_prepare/removedocument. See here for some examples:
    http://www.sommarskog.se/arrays-in-sql-2005.html#XML

    Finally, "BULK INSERT" is a seprate command in SQL Server, used for loading data from a file, which does not seem to be the case here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman Toth Tuesday, January 29, 2013 8:13 PM
    • Marked as answer by Iric Wen Thursday, January 31, 2013 8:29 AM
    Wednesday, January 23, 2013 8:56 AM

All replies