Insert more that 1 row at a time

Question

Hi all, How can we add more that 1 row data to a table in sql server at a time?

shashi kiran Jillepally · Answer

Hi Suma.. just check this may be helpful to you its inserts data(more than one Row at a time(In Single post back) data(All rows of the gridview) is inserted into sql type table(Tbl_LeaveType1) at once first and than to original(Tbl_Leave1) create table Tbl_Leave1 ( LType varchar(5), JAN varchar(20), FEB varchar(20) ) create type Tbl_LeaveType1 as table ( TLType varchar(5), TJAN varchar(20), TFEB varchar(20) ) create procedure Sp_Leave ( @Leave Tbl_LeaveType1 readonly ) as insert into Tbl_Leave1(LType,JAN,FEB)select TLType,TJAN,TFEB from @Leave; Source Code: '> '>'> '> .Cs(C#) using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Configuration; public partial class Test1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { TempTbl(); } } public void TempTbl() { //Here i have load a gridview with empty data dynamically with Temprary table in .cs file. DataTable Dt = new DataTable(); Dt.Columns.Add(new DataColumn("Leave", typeof(String))); Dt.Columns.Add(new DataColumn("JAN", typeof(String))); Dt.Columns.Add(new DataColumn("FEB", typeof(String))); Dt.Rows.Add("CL"); Dt.Rows.Add("EL"); Dt.Rows.Add("SL"); Dt.Rows.Add("ML"); Dt.Rows.Add("OL"); Gv1.DataSource = Dt; Gv1.DataBind(); } public DataTable GetGv() { DataTable Dt1 = new DataTable(); Dt1.Columns.Add(new DataColumn("Leave", typeof(String))); Dt1.Columns.Add(new DataColumn("JAN", typeof(String))); Dt1.Columns.Add(new DataColumn("FEB", typeof(String))); DataRow Dr; foreach (GridViewRow RW in Gv1.Rows) { Label Lv = (Label)RW.FindControl("L"); string Leave = Lv.Text; TextBox J = (TextBox)RW.FindControl("t1"); string JAN = J.Text; TextBox F = (TextBox)RW.FindControl("t2"); string FEB = F.Text; Dr = Dt1.NewRow(); Dr[0] = Lv.Text; Dr[1] = J.Text; Dr[2] = F.Text; Dt1.Rows.Add(Dr); } return Dt1; } public void SaveData() { DataTable Dt2 = new DataTable(); Dt2 = GetGv(); //using web.config file string strcon = ConfigurationManager.ConnectionStrings["conn"].ConnectionString; //or you can write query string// using (SqlConnection con = new SqlConnection(strcon)) { SqlCommand cmd = con.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Sp_Leave"; SqlParameter P1 = cmd.Parameters.AddWithValue("@Leave", Dt2); con.Open(); cmd.ExecuteNonQuery(); } } protected void Btn1_Click(object sender, EventArgs e) { SaveData(); } }

shashi kiran Jillepally · Answer

Hi suma just check code which a i have written above, it converts the gridview [consisting More than 1 row may be 10 or more] into a DataTable then to a Parameter and stores to a temp in sqlserver and save all rows to actual table one by one.[Iterates in back end] in one postback

Vulpes · Answer

Well, if you have 4 or 5 records to insert in the table, then the 'multi-insert' syntax I gave in my opening post is less tedious to use than using individual insert statements but it starts to become unwieldy when you have 10 or more. In that situation, there will probably be some relationship between the records you're inserting and you could therefore insert the records one by one using a loop. You could also use SqlBulkCopy as Vithal suggested though, for that to be worthwhile, I'd say you really need to be inserting 100 or more records at a time.

Jignesh Trivedi · Answer

hi, agree with both. it is also depending on situation when you want add multiple record. suppose you want to add records from table than you can use Insert into table1 (a,b,c) select a,b,c from tableb hope this will help you.

Vithal Wadje · Answer

one more thing we can also use sqlbulkcopy

Vulpes · Answer

If there's just a few records to insert you can do something like this: INSERT INTO Table (Column1, Column2) VALUES (Value11, Value12), (Value21, Value22), (Value31, Value32)

Suma M · Answer

Thanks everybody for the help... :)

Suma M · Answer

Hi all, Thanks for replying.. I want to know how can i do this... If i want to add say 4- 5 rows to a table and in some cases how can i add 10 or more... In such scenario what to do? And is it possible? Then how? Have you`ll come across such scenarios?

Insert more that 1 row at a time

Answers (8)