Inserting data from one table to another repeatedly

Sivajihero Hero

I have two tables table1 and table2. I need to insert table values from table1 to table2. The table 1 contain columns nnum,code,qnty 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 <= Convert.ToInt32(ds2.Tables[0].Rows[i]["Qty"].ToString()) ; 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 A2 1

1 A3 1

I need to get the output table2 like this

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sivajihero Hero10y ago
@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 <= Convert.ToInt32(ds2.Tables[0].Rows[i]["Qty"].ToString()) ; 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.
0
Manas Mohapatra10y ago
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.
0
Sivajihero Hero10y ago
@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.
0
Manas Mohapatra10y ago
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(1,1), 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/
0
Sivajihero Hero10y ago
Is there any other method in asp.net
0
Raja T10y ago
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/
0