I have two data tables in sql. I need to insert data feom one table to another. My first data table Table1 contain data column Code,Model,Num,Qty and second table Table2 also contain the same. But I need to insert the data from table1 to table2 multiple times. If Qty in Table1 is 4 then, insert the data of
Table1 for 4 times according to the quantity.
EX: Table1
Code Model Num Qty
a1 m12 12 4
a2 n42 10 2
Then the result may be in Table2 is
Code Model Num Qty
a1 m12 12 1
a1 m12 12 1
a1 m12 12 1
a1 m12 12 1
a2 n42 10 1
a2 n42 10 1
How can I achieve this? This is my code for insertion. But its not working.
string connetionString = null;
SqlConnection connection ;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
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();
for (i = 0; i <= Convert.ToInt32(ds.Tables[0].Rows[0]["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)
{
MessageBox.Show(ex.ToString());
}