CODEWITHSUNDEEP
xmlsql-server-2005c#-4.0insertunion-all
FMFF
FMFF

Reputation: 1718

Multiple Inserts with XML vs INSERT...VALUES...SELECT...UNION ALL

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.

Upvotes: 1

Views: 294

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

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.

Upvotes: 1

Related Questions