And on SQL Server < 2008 where TVPs aren't an option and the data set is large enough (I was importing millions of rows from user uploaded CSV files) I've had g

And on SQL Server < 2008 where TVPs aren't an option and the data set is large enough (I was importing millions of rows from user uploaded CSV files) I've had good experiences building chunks of XML (couple thousand rows at a time) on the client side and shipping that off to the client as a single XML parameter. Although, in practice, when importing complex data in real-time there's a lot of experimentation required to determine where the real bottlenecks are and this article skims the surface of the complexity involved in a real import. For example, do you do your lookups inside your stored procedure or does your application layer deal with all of that and send off pre-prepared data? If the latter, you reduce the amount of time the table is locked whilst adding rows since there are no joins and lookups in the insert, and this may be more performant overall than the alternative. You can also reduce the duration of any transaction lock you may need to hold for importing all the data which ultimately makes the system more responsive during large imports. Furthermore the "prepare-first" approach makes error reporting more practical since it's generally easier to do and report validation in the application layer.