Processing multiple data in a single call SQL Query
Say you want to insert multiple rows of data in single shot query, how you accomplished that ?
There are several ways. Two of them I’ve used, they are:
1. Via XML parameter.
Bundle multiple data values into XML then pass to a procedure or statement. In a procedure or statement, use Nodes method to process that data in a single call. Here’s one of the example I’ve found in the Net : http://www.thinkingcog.com/post/2014/02/15/Inserting-multiple-rows-in-SQL-Server-database.aspx
2. Via Table-Valued parameter.
Here you have to create table type first before we can use table-value parameter in procedure. Completed how-to and sample can be found in MSDN link here : https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx . But remember it only available at SQL 2008 or higher.