Chuah Cheng Jun Chuah Cheng Jun - 7 months ago 8
SQL Question

Insert multiple generated value into table (Solved)

First thing, my question is quite complicated and I am not really proficient to explain it in details, so I would like to say sorry in advance.

Ok, here is the thing. I execute this query

string count = "SELECT Count(*) FROM Student WHERE IntakeID = 'MYVALUE'"


And it would return the number of 10. So I continue with this code:

SqlCommand cmd = new SqlCommand(count, conn);
int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());
for (int x = 0; x < temp; x++)
{
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ('" + getUniqueKey() + "','(SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE')')
}


PS: getUniqueKey() is a method to get my generated unique key.

Is this piece of code technically correct? The result I want to have is something like below:

+-----------+-------------+
| DocketNo | StudentID |
+-----------+-------------+
| 18590394 | TP123456 |
| 09141563 | TP012457 |
| 58293495 | TP049185 |
+-----------+-------------+


If you are unclear of my question, I will try my best to make it clearer. Sorry for inconvenience.

UPDATE (ANSWER):
With the help from Paparazzi (Thanks!) for his code, I modified and come up with my own solution.

string count = "SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE'"
SqlCommand cmd = new SqlCommand(count, conn);
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
var loop = true;
while (loop)
{
loop = rdr.Read();
if (!loop)
{
//When end of rows and no more data to be retrieve, it removes the last "," from the query.
char[] trimChar = { ',' };
string newQuery = query.TrimEnd(trimChar);
cmd.CommandText = newQuery;
}
else {
query += "('" + GetUniqueKey() + "','" + rdr.GetString(0) + "')";
query += ",";
}

}
}
cmd.ExecuteNonQuery();
conn.Close();


So actually while looping on the SqlDataReader, the query would generate in the background something like:

INSERT INTO Docket(DocketNo, StudentID) VALUES ('1562456','TP028800'), ('1465446','TP028801'),..........('4939104','TP028810'),


Take note there will be a "," comma at the end of the query because of the
query += ",";
. And when the SqlDataReader returns no more rows, it would execute the
if(!loop)
statement to remove the last "," (comma) from the query.

If you are unclear what is the
+=
for, read more at https://msdn.microsoft.com/en-us/library/sa7629ew.aspx

Answer
string count = "SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE'"
SqlCommand cmd = new SqlCommand(count, conn);
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ";
conn.Open();
bool first = true;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        if (first)
          first= false;
        else 
           query += " ,"
        query += "('" + getUniqueKey() + "','" + rdr.GetString(0) + "')";  
    }
}
if (!first)
{
    cmd.CommandText = query;
    cmd.ExecuteNonQuery();
}
conn.Close();

StringBuilder is faster than += on string
And multiple values is limited to 1000 (I think) but WAY more efficient than individual inserts
So if you can get more than 1000 then need to add a counter and fire off the insert