Adding a row with variable in sql

Question

I'm trying to add a calculated row to a table using the Insert Into. So roughly i'm trying to add and split the following:

     [RFC]       [Tons]
[1]   NAME 1       30
[2]   NAME 2       50
[3]   NAME 2       30
[4]   NAME 2       20

So that I get:

     [RFC]       [Tons]
[1]   NAME 1       30
[2]   NAME 2       42
[3]   NAME 2       58

As you can see, I added all of Name 2 and then divided into two rows: one that has 42% of the total, and one for 58% of the total.

Adding the calculated variable is pretty straightforward:

First I declare my variable which is

DECLARE @TonsE int;
SET @TonsE = CASE 
WHEN 
(SELECT SUM(mkt_impotemporal.Tons)*0.42 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) is null THEN 0 
ELSE 
(SELECT SUM(mkt_impotemporal.Tons)*0.42 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) 
END;

Then I add:

INSERT INTO mkt_impotemporal 
VALUES 
('EHP040219KX0',
@TonsE)

However, now i'm stuck with a new row with 42% of the total, but can't seem to change the remaining rows to reflect only 58% of their original values, even if I run an update BEFORE I insert the new row (I'm not sure how it works but it seems as though the variable does not "store" the number for another query).

Any ideas on how I can achieve this? Thanks in advance!

What database system you're using?

Oleksandr Fedorenko
– Oleksandr Fedorenko

2013-12-19 16:47:24 +00:00
Commented Dec 19, 2013 at 16:47 — Oleksandr Fedorenko
– Oleksandr Fedorenko, Commented Dec 19, 2013 at 16:47
@AlexanderFedorenko SQL 2012.

eflores89
– eflores89

2013-12-19 17:30:53 +00:00
Commented Dec 19, 2013 at 17:30 — eflores89
– eflores89, Commented Dec 19, 2013 at 17:30

user3133172 · Accepted Answer · 2013-12-26 21:21:31Z

1

I think the easiest thing would be to use a second variable and compute its value before doing your inserts.

DECLARE @TonsE int;
SET @TonsE = CASE 
WHEN 
(SELECT SUM(mkt_impotemporal.Tons)*0.42 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) is null THEN 0 
ELSE 
(SELECT SUM(mkt_impotemporal.Tons)*0.42 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) 
END;

DECLARE @TonsE2 int;
SET @TonsE = CASE 
WHEN 
(SELECT SUM(mkt_impotemporal.Tons)*0.58 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) is null THEN 0 
ELSE 
(SELECT SUM(mkt_impotemporal.Tons)*0.58 FROM mkt_impotemporal where RFC='EHP040219KX0' GROUP BY RFC) 
END;

--DELETE FROM mkt_impotemporal WHERE RFC = 'EHP040219KX0'

INSERT INTO mkt_impotemporal 
VALUES 
('EHP040219KX0',
@TonsE);

INSERT INTO mkt_impotemporal 
VALUES 
('EHP040219KX0',
@TonsE2)

answered Dec 26, 2013 at 21:21

user3133172

887 bronze badges

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

1 Comment

eflores89 Over a year ago

I had actually created another table to store the numbers before, erase and copy again, but your solution is much more elegant/efficient. Thanks!!

Collectives™ on Stack Overflow

Adding a row with variable in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related