Inserting data from one table to another repeatedly

Question

I have two tables table1 and table2. I need to insert table values from table1 to table2. The table 1 contain columns nnumcodeqnty and table2 also same column. I need to insert table1 value to table2 and repeat the insertion according to the qnty value.But my current code is not working properly. Here I am attaching the format I need to get. The Table2 in the image is the thing I need to get. protected void Button15_Click3(object sender EventArgs e) { string connetionString = null; SqlConnection connection ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; int j = 0; connetionString = Data Source=xxxxx;Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxxxx; connection = new SqlConnection(connetionString); try { connection.Open(); adapter.SelectCommand = new SqlCommand(insert into Table2(Code Model Num Qty) select Code Model Num Qty from Table1 connection); adapter.Fill(ds); connection.Close(); String str2 = select * from Table2;; SqlCommand xp2 = new SqlCommand(str2 con); con.Open(); SqlDataAdapter da2 = new SqlDataAdapter(); da2.SelectCommand = xp2; DataSet ds2 = new DataSet(); da2.Fill(ds2 Table2); con.Close(); for (i = 0; i { String str1 = insert into Table2(Code Model Num Qty) select Code Model Num Qty from Table1;; SqlCommand xp1 = new SqlCommand(str1 con); con.Open(); SqlDataAdapter da1 = new SqlDataAdapter(); da1.SelectCommand = xp1; DataSet ds1 = new DataSet(); da1.Fill(ds1 Code); GridView1.DataSource = ds1; con.Close(); } } catch (Exception ex) { Response.Write(ex.ToString()); } Example: Table1 Num Code Qty 1 A1 3 1 A2 2 1 A3 2 Table2 Num Code Qty 1 A1 1 1 A1 1 1 A1 1 1 A2 1 1 A2 1 1 A3 1 1 A3 1 I need to get the output table2 like this

Sivajihero Hero · Answer

@Manas Could you pls check my above code. In that code the i value will be inremented from 1 to qty value. for (i = 0; i So if my first qnty value is 3 then it will execute this statement three times String str1 = insert into Table2(Code Model Num Qty) select Code Model Num Qty from Table1;; So it automatically insert my other two columns also three times but there qnty is only 2. That is the problem. Now the table will insert the rows totaly 7 times each row and total rows 21. So problem is with this staement. String str1 = insert into Table2(Code Model Num Qty) select Code Model Num Qty from Table1;; In here can I change it to String str1 = insert into Table2(Code Model Num Qty) ds2.Tables[0].Rows[i][Qty] like this. So it will insert only first row at a time.

Manas Mohapatra · Answer

Yes you can do it through C# code but it will create performance overhead. So try it through stored procedure. Whatever logic I implemented you need to do the same with C# code. Please read my previous answer.

Sivajihero Hero · Answer

@Manas I s there any way I can do it by coding in asp.net without using procedure.Beacuse in my above program it will insert but the numbers is not same.It will insert all code 3 times. It repeat all the rows according to all quantity.

Manas Mohapatra · Answer

Hi Siva You are doing correct to accomplish your requirement. You are looping over quantity value and executing the Insert statement each time. It would be better if you use a StoredProcedure(SP) instead of doing application side. All these looping functionality will present inside SP. So that at single shot to database call you will achieve your functionality. However in your code it is closing and opening so many times to DB calls which is not good for performance. Here is the complete code for you logic: CREATE Procedure uspAddRecords() AS BEGIN Create TABLE #TempTable(ID int identity(11) Code varchar(10) Model varchar(10) Num int Qty int); insert into #TempTable(Code Model Num Qty) select Code Model Num Qty from Table1; Declare @TempID int @Code varchar(10) @Model varchar(10) @Num int @Qty int; WHILE() BEGIN SET @Qty = 0; select TOP 1 @TempID= ID @Code = Code @Model = Model @Num = Num @Qty = Qty from #TempTable ORDER BY 1; WHILE(@Qty >= 1) BEGIN insert into Table2(Code Model Num Qty) select @Code @Model @Num 1 from Table1; SET @Qty = @Qty - 1; END DELETE FROM #TempTable WHERE ID = @TempID; END DROP TABLE #TempTable; END Follow below links to call stored procedure from ASP.NET: http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm http://www.c-sharpcorner.com/uploadfile/01fc26/how-to-use-stored-procedure-in-simple-easy-way-with-web-config-file/

Sivajihero Hero · Answer

Is there any other method in asp.net

Raja T · Answer

Hi Sivajihero For your requirement it is easy manage in sql server. you can use triggers. Trigger: Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers. more details check out below links http://www.dotnet-tricks.com/Tutorial/sqlserver/OPUH170312-After-Trigger-Instead-of-Trigger-Example.html http://www.tech-recipes.com/rx/41414/sql-server-coding-the-after-insert-trigger-in-sql-server/