1

For SQL Server 2005, if I want to insert about 20 or 30 rows each with three columns, which approach is faster and efficient? The calling application is a C# 4.0 .Net console application.

a) Passing the values as XML to a stored proc and parsing it and doing the insert
OR
b) Construct a SQL command with INSERT...VALUES... SELECT() UNION ALL as suggested here: SQL Server 2005: Insert multiple rows with single query ?

Thank you.

Community
  • 1
  • 1
FMFF
  • 1,652
  • 4
  • 32
  • 62

1 Answers1

1

Did you try them? To be honest, for 20 or 30 rows, you'd be really hard pressed to find a difference. At a larger scale, I would expect the cost of extra data and parsing involved with XML to extract data would make it the less efficient choice. In SQL Server 2008 there is a more efficient VALUES() constructor that doesn't require UNION ALL, and there is also a new approach called table-valued parameters.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thank you @Aaron Bertrand. I tried, but as you correctly stated, I found myself spending considerable amount of time in writing the logic for constructing and parsing the XML. I could not spot any significant improvement in performance of the XML based approach for task on hand. I'm aware of the SQL Server 2008's new INSERT VALUES construct, but alas, updating SQL Server is not upto me. Thanks again. – FMFF Feb 21 '12 at 19:16