C# Dynamic SQL INSERT ... SELECT

Question

I am using the INSERT ... SELECT syntax to select existing rows from a table and insert then into another table. In addition to the existing data from each row I also need to add a BillingID and TimeStamp. Because these fields are in the SELECT part of the SQL statement I cannot parametrize them. I solved the TimeStamp issue using the SQL function NOW(), however I am still left with BillingID which I have added to the query via sting concatenation as follows:

static void UpdateMonthlyData(int BillingID, DateTime HistoryDate, int CompanyID)
{   
        String conString = ConfigurationManager.ConnectionStrings["xxx"].ConnectionString;
        MySqlConnection connection = new MySqlConnection(conString);

        String command = "INSERT INTO MonthlyData SELECT " + BillingID + ", d.*, NOW() "  
                       + "FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate";

        MySqlCommand cmd = new MySqlCommand(command, connection);
        cmd.Parameters.Add(new MySqlParameter("@CompanyID", CompanyID));
        cmd.Parameters.Add(new MySqlParameter("@HistoryDate", HistoryDate));

        cmd.CommandType = CommandType.Text;

        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();                          
}

I am not concerned with SQL Injection as this is a console app that is run on an automated schedule and has no user interaction whatsoever. (BillingID is auto-generated). Despite that, I don't like using concatenated strings as they are not very readable. Is there a more elegant way of doing this?

Edit:

To sum things up, I thought that since since you cant do this:

SELECT @field FROM @table

I presumed that parameters are not allowed in the SELECT part of a SQL statement. However since I am specifying a value in the select statement rather than selecting a column, as @cdhowie pointed out I can use a parameter there. In essence my query translated is something like this:

SELECT 25 FROM table_name, not  SELECT field FROM table_name

So now thanks to @cdhowie I understand that a parameter can be anywhere a literal value can be

I think you should take a look on prepared statements. Which RDMS are you using? SQL server? MySQL? — Barranka, Apr 22 '13 at 20:56
*"Because these fields are in the SELECT part of the SQL statement I cannot parametrize them."* Why? What if you add an `AS` alias for the column? — cdhowie, Apr 22 '13 at 20:56
@cdhowie, he's supplying the value for the column, the name is unimportant. — nathan gonzalez, Apr 22 '13 at 21:01
I am using prepared statements for the WHERE parameters, but I am under the impression they cannot be used in the select part of the statement — Louise Eggleton, Apr 22 '13 at 21:05
@nathangonzalez But I cannot imagine why binding a parameter in the SELECT clause would cause an error, unless there is some MySQL-specific behavior going on here. This sounds vaguely familiar, and I recall cases where adding a column alias (even if the alias is never referenced) magically made everything work. — cdhowie, Apr 22 '13 at 21:08
@LouiseEggleton Have you actually tried it yet? To my knowledge, a query parameter is valid anywhere that a literal value would be, assuming that the query parameter is of the correct type (e.g. `... LIMIT @Foo` should work as long as you bind an integer to the Foo parameter). — cdhowie, Apr 22 '13 at 21:09
This post here http://stackoverflow.com/questions/3128582/table-name-and-table-field-on-sqlparameter-c states "Sql can't have parameters on fieldnames or tablenames, just on values". I took that to mean only values in the where clause. — Louise Eggleton, Apr 22 '13 at 21:14
@cdhowie, i get what your point was now. i think you might be right in that adding an alias might push that through properly. — nathan gonzalez, Apr 22 '13 at 21:15
However I now realize that since I am not selecting a field name but am inserting a literal value that I probably misinterpreted the other post — Louise Eggleton, Apr 22 '13 at 21:17
@LouiseEggleton, give it a shot without an alias, and if that doesn't work, try adding an alias — nathan gonzalez, Apr 22 '13 at 21:19
@cdhowie, since it was you who made me question my assumption that parameters can't be in the SELECT part of the clause can you put your comment in a question so I can accept it? — Louise Eggleton, Apr 22 '13 at 21:49

score 3 · Accepted Answer · answered Apr 22 '13 at 21:56

A query parameter is valid anywhere that a literal value would be, assuming that the query parameter is of the correct type (e.g. ... LIMIT @Foo should work as long as you bind an integer -- or something the SQL server can successfully convert to an integer -- to the Foo parameter). This assumes no particular quirks in the SQL dialect you are using, of course.

In other words, there is no reason that you shouldn't be able to pass BillingID using a query parameter.

score 2 · Answer 2 · answered Apr 22 '13 at 21:00

You could use String.Format for any safe fields that cannot be added via parameterization:

String command = String.Format("INSERT INTO MonthlyData SELECT {0}, d.*, NOW() FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate", BillingID);

score 2 · Answer 3 · answered Apr 22 '13 at 21:00

2

You can use .Net string.format

Example :

    string query = string.format{"insert into MonthlyData select {0} from CurrentData",
                                  BillingId);

You can parameterize your query a lot more with cleaner code.

Good luck

answered Apr 22 '13 at 21:00

Dan Dinu

32,492
24
78
114

I will keep this in mind for cases when I can't paramtrize the query such as when I want a dynamic column or table name – Louise Eggleton Apr 23 '13 at 16:18
Although I used cdhowies solution for the problem I had at that time, I did run into a different scenario today (dynamic column name), where your answer was perfect for the task at hand. Thanks. – Louise Eggleton Nov 21 '13 at 15:42

C# Dynamic SQL INSERT ... SELECT

3 Answers3