How to execute INSERT..SELECT statement

Question

I want to execute (INSERT .. SELECT ) statement like this :

cmdTxt.Clear();
cmdTxt.Append(" INSERT INTO  aast:sc1pen ");
cmdTxt.Append(" SELECT action_month,action_year,200,emp_num,penalty_action , ");
cmdTxt.Append("  'APPLY ' || penalty_reason || ' day ' , 0 , 0 ");
cmdTxt.Append(" FROM sc2pen WHERE sal_year = ? and sal_month = ? and penalty_type = 1 and pay_type = 0 ");
myIfxCmd.CommandText = cmdTxt.ToString();

myIfxCmd.Parameters.Clear();

myIfxCmd.Parameters.Add("sal_year", IfxType.Integer);
myIfxCmd.Parameters.Add("sal_month", IfxType.Integer);

myIfxCmd.Parameters[0].Value = penaltyDt.Rows[0]["sal_year"];
myIfxCmd.Parameters[1].Value = penaltyDt.Rows[0]["sal_month"];

Now I'm confused should i use

myIfxCmd.ExecuteNonQuery();

To execute query like this although it include read operation ?

Yes, use myIfxCmd.ExecuteNonQuery(); because you are not returning a result set to the client. The select is only being used to source the data to populate the insert. — Murray Foxcroft, May 16 '16 at 08:31

score 1 · Accepted Answer · answered May 16 '16 at 08:37

1

Firstly, it is not a good idea to do inline SQL as this does not make for maintainable code. This should be done in the database inside a stored procedure ideally - especially for inserting data into an existing table. Then you can execute the SP and return data inside an output parameter.

However, if you really want to go down this route then executing

    ExecuteNonQuery()

...will not return any data, only the records affected. If you want to return some data, like the id of the newly inserted record, you want to use

    ExecuteScalar()

...which will allow a scalar value to be returned.

answered May 16 '16 at 08:37

Steveo

32
2

1

Your opening statement is purely opinion based. If you are going to make a statement like that you should back it up with valid reasons. Many developers have legit reasons for keeping their SQL code inline. – Fred May 16 '16 at 08:43
1

FYI: http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code – Fred May 16 '16 at 08:45
Yes it is my opinion @Fred. I suppose it depends on the scale of the project you are working on and the number of different database requests you make. I was simply saying that putting SQL in the database is a nicer, cleaner, more secure, more efficient, more maintainable, more reusable solution. Good arguments for and against inline SQL in your link but I would always side on putting a database query language where it belongs...in the database. – Steveo May 16 '16 at 09:12
Stalkoverflow.com has only a one SP :) – Anyname Donotcare May 16 '16 at 09:46

How to execute INSERT..SELECT statement

1 Answers1