SqlCommand - Select multiple rows and INSERT them into table

Question

Hello I'm trying to SELECT multiple rows from table and INSERT them into another I thought that it can be done as following:

This part should select multiple rows:

 string sqcom = "SELECT text,castka,rocnik FROM zajsluz WHERE akce='"+tentoradek+"' and rocnik='"+klientClass.Rocnik()+"'";

                    SqlCommand sc = new SqlCommand(sqcom,spojeni);

                    spojeni.Open();
                    sc.ExecuteNonQuery();
                    spojeni.Close();

This is how I try to INSERT selected rows from SqlCommand sc:

  string sqlcom2 = "INSERT INTO zajsluz(akce,text,castka,rocnik) values (@akce,@text,@castka,@rocnik)";

                    SqlCommand sc2 = new SqlCommand(sqlcom2, spojeni);
                    sc2.Parameters.AddWithValue("@akce", klientClass.Rocnik());
                    sc2.Parameters.AddWithValue("@text", ); // I dont know how to define this parameter according to what was selected in SqlCommand sc
                    spojeni.Open();
                    sc2.ExecuteNonQuery();
                    spojeni.Close();

Now I'm wondering hwo can I insert into "@text" (sc2) parameter values from SqlCommand "sc" would you please help me solve this out?

Thanks in advance

Edit: ¨

this is what I tried:

  DataSet dt2 = new DataSet();
                    SqlDataAdapter SDA2 = new SqlDataAdapter("SELECT text,castka FROM zajsluz WHERE akce='" + tentoradek + "' and rocnik='" + klientClass.Rocnik() + "'", spojeni);
                    SDA2.Fill(dt2);
                    spojeni.Close();
 string sqlcom2 = "INSERT INTO zajsluz(akce,text,castka,rocnik) values (@akce,@text,@castka,@rocnik)";

                    SqlCommand sc2 = new SqlCommand(sqlcom2, spojeni);
                    sc2.Parameters.AddWithValue("@akce", zakce.Text);
                    sc2.Parameters.AddWithValue("@rocnik", klientClass.Rocnik());
                    sc2.Parameters.AddWithValue("@text", dt2.Tables[0].Columns["text"]);
                    sc2.Parameters.AddWithValue("@castka", dt2.Tables[0].Columns["castka"]);
                    spojeni.Open();
                    sc2.ExecuteNonQuery();
                    spojeni.Close();

You need a SqlDataReader on the first query returned with ExecuteReader() and then you need to loop through the reader. — Wiktor Zychla
– Wiktor Zychla, Commented Sep 3, 2013 at 10:06
first execute the select statement and store it in a dataset.Then using ds.tables[0].[columnname] pass parameter and insert it. — Sasidharan
– Sasidharan, Commented Sep 3, 2013 at 10:07

Ronak Patel · Accepted Answer · 2013-09-03 10:12:40Z

1

You can directly use insert into & select combination

 string sqcom = "INSERT INTO zajsluz(akce,text,castka,rocnik) SELECT rocnik,text,castka,rocnik FROM zajsluz WHERE akce='"+tentoradek+"' and rocnik='" + klientClass.Rocnik() + "'"

    SqlCommand sc = new SqlCommand(sqcom,spojeni);

                        spojeni.Open();
                        sc.ExecuteNonQuery();
                        spojeni.Close();

answered Sep 3, 2013 at 10:12

Ronak Patel

2,60219 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marek Over a year ago

Thanks for your reply can I define (when doing INSERT INTO) for exampel akce as following parameter: sc2.Parameters.AddWithValue("@akce", klientClass.Rocnik()); Because I will need to change values in 2 columns.

Ronak Patel Over a year ago

Yes ofcourse you can do it. Set its value as @columnname in the select part and then use AddWithValue to set the parameter value

Hedinn · Accepted Answer · 2013-09-03 10:12:38Z

I would try to do this in a single statement if that is possible, i.e. you aren't doing anything to the data in between the two statements.

string sqlcom = "INSERT INTO zajsluz(akce,text,castka,rocnik) SELECT akce,text,castka,rocnik FROM zajsluz WHERE akce='"+tentoradek+"' and rocnik='"+klientClass.Rocnik()+"'";

SqlCommand sc = new SqlCommand(sqcom,spojeni);

                spojeni.Open();
                sc.ExecuteNonQuery();
                spojeni.Close();

Brian · Accepted Answer · 2013-09-03 10:19:31Z

0

Another option would be to use a SQL DataSet/DataTable, which allows you to query and return from SQL an entire table, or a set of rows, that you can then update, delete or insert into. It's described in the following MS article: http://support.microsoft.com/kb/326009/en

answered Sep 3, 2013 at 10:19

Brian

3,7132 gold badges25 silver badges34 bronze badges

1 Comment

Marek Over a year ago

Thank you for your answer, may you please see my edit why the mapping doesnt exist in this case? Thank you so much

Fabio · Accepted Answer · 2013-09-03 11:21:02Z

This summary answer for your question:

StringBuilder query = new Stringbuilder();
query.AppendLine("INSERT INTO zajsluz(akce,text,castka,rocnik) ");
query.AppendLine("(SELECT @akce, text, castka, @rocnik");
query.AppendLine("FROM zajsluz WHERE akce=@Tentoradek");
query.AppendLine("AND rocnik=@rocnik)");

SqlCommand sc2 = new SqlCommand(sqlcom2, spojeni);
sc2.Parameters.AddWithValue("@Tentoradek", tentoradek);
sc2.Parameters.AddWithValue("@akce", zakce.Text);
sc2.Parameters.AddWithValue("@rocnik", klientClass.Rocnik());

spojeni.Open();
sc2.ExecuteNonQuery();
spojeni.Close();

And this take away your problems/error Object reference.... when reading of DataSet ...

Collectives™ on Stack Overflow

SqlCommand - Select multiple rows and INSERT them into table

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Related