Adding multiple values to a table

Question

I need to add multiple values to a table which are taken from other table or calculated using other tables. I am using a code as shown below. Please tell how can we calculate the values which are fetched from other table. I need to insert to a table sales(scriptname,accnum,sharesbought,sharessold,remshares) from the table transac(tid,sciptname,accnum,transactype,Quantity,date). Based on the transac type I need to find whether it is sharebought or sharesold and remainingshare is sharebought-sharesold;

I am using .Net to do this please tell me that whether the code I am using it is correct or not.

    SqlConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                Con.Open();
                string insertsale = "INSERT INTO sales s (accnum, scriptname, shares_bought,sharessold,rem_shares) select case when t.transactype == 'sell' then s.accnum = t.accnum, s.scriptname = t.scriptname. s.shares_bought = 0,s.shares_sold=t.quanity, (s.shares_bought-s.shares_sold) as s.rem_shares 
else
 s.accnum = t.accnum, s.scriptname = t.scriptname. s.shares_bought = t.quanity,s.shares_sold=0, (s.shares_bought-s.shares_sold) as s.rem_shares from transac t";
                SqlCommand cmd = new SqlCommand(insertsale, Con);
                cmd.ExecuteNonQuery();
                Con.Close();

Rahul · Accepted Answer · 2015-09-13 14:32:21Z

2

Your posted INSERT statement is wrong and weird. You can do this in a single SQL query like below. If there is any change required, you can follow along the line and modify the given query accordingly.

INSERT INTO sales(accnum, scriptname, shares_bought,sharessold,rem_shares) 
select accnum, scriptname,
case when transactype = 'sell' then 0 else quanity end as shares_bought, 
case when transactype <> 'sell' then quanity else 0 end as sharessold,
case when transactype = 'sell' then (0 - quanity) else (quanity - 0) end as rem_shares
from transac;

answered Sep 13, 2015 at 14:32

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

Adding multiple values to a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related