Bulk insert in SQL

SQL Bulk Insert in a single line

     Bulk insert is possible in SQL, every set data data's are passed inside the "()" (brackets), every set have should separate with "," (comma).

Example

insert into tbl1(col1,col2) values(val1,val2), (val3,val4), (val5,val6), (val7,val8);

Where to use

     In some situation we need to run loop to save the same column data row by row, so there we can use this bulk insert.
   
     In below example if you are using single insert query you need to contact server 10 times.

<script>
for(int i=0;i<10;i++)
{
 PageMethods.web_fun(i);  //Passing the value to server in asp.net, other also same problem
}
</script>

In SQL

alter proc sv_in_sql
@value int
as
begin
 insert into Tbl1(id) values(@value);
end

From the above example, the PageMethod will connect to the server 10 times and it will execute the same insert query 10 times repeatedly.

But if you are used bulk insert query just you need to connect the server only once.

<script>
var ar_variable=[];
for(int i;i<10;i++)
{
  ar_variable.push(i);
}
var strvalue= ar_variable.join("),(");
strvalue = "(" + strvalue + ")";
PageMethods.web_fun(strvalue);
</script>

In SQL

alter proc sv_in_sql
 @value varchar(max)
as
begin
  declare @strQuery varchar(max);
  set @strQuery= 'insert into Tbl1(id) values'+@TypeSetQuery
 exec(@strQuery);
 end

In the above methods, the server will call only once and all the values are insert in a single query.

ID	Name
0	NULL
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL
6	NULL
7	NULL
8	NULL
9	NULL