Add calculated column to table ssms (sql query)

Question

Im trying to add and save a new calculated column to the table energy, using the following sql query:

ALTER TABLE energy
  ADD energie_active_1 float

SELECT *,
       energie_active - LAG(energie_active, 1, energie_active)
                          OVER (
                            PARTITION BY DESIGNATION
                            ORDER BY TS)
FROM   energy

But i get the column energie_active_1 empty and a new column without name with the calculated values (as shwon in the image) Query result

I dont use sql queries a lot, so any help or advice would be very appreciated.

Thank you.

Your sql is 1) creating a column called energie_active_1 with a datatype as float. then 2) running a completely separate statement which is a query. In other words, you created a column, but not a calculated column. Then you ran a normal query. — topsail, Jun 19 '22 at 14:38
I'm not really sure that computed columns can use lag functions - dunno. You can certainly just use your query and not create a calculated column.... — topsail, Jun 19 '22 at 14:39
Rather than add the column to the table, just alias the column in the query as `energie_active_1`. One cannot use windowing functions in a computed column expression. — Dan Guzman, Jun 19 '22 at 14:40
Cannot define a data type for a table calculated field. Try: `energie_active_1 AS SELECT ...`. But as already noted, some functions cannot be used in table calculated field. Just build a query with the calc. — June7, Jun 19 '22 at 14:47
Ok i see whats wrong now, but i need to have this calculated column in the table, cause its used by a Power bi dashboard to show the energie_active_1; and i cant do these calculations on Power bi "i get not enough memory" cause table contains a lot of rows. — oussama AY, Jun 19 '22 at 14:49

score 0 · Answer 1 · edited Jun 21 '22 at 07:57

0

alter table TABLE_NAME
add [column_name] as (**COLUMN-SQL**)

edited Jun 21 '22 at 07:57

Wai Ha Lee

8,598
83
57
92

answered Jun 20 '22 at 05:22

Vaibhav Pathade

41
3

score 0 · Answer 2 · answered Jun 20 '22 at 08:33

Thank you all for your answers. So this is what i have came with, not sure if its the correct way to do it. But its working for me :)

INSERT INTO energy ( energy.TS
     , energy.DESIGNATION 
     , energy.energie_active,
     energy.energie_reactive,
     energie_active_1 ) (SELECT energy.TS
     , energy.DESIGNATION 
     , energy.energie_active,
     energy.energie_reactive,energy.energie_active-LAG(energy.energie_active, 1, energy.energie_active) OVER (PARTITION BY energy.DESIGNATION ORDER BY energy.TS)FROM energy)

Now i get the new column in the same table.

If it works for you then that's all that matters but this is not what you asked for - what happens if any existing row has `energie_active` column updated? PS you can remove the repeated table name from the insert and select, it's not needed. — Stu, Jun 20 '22 at 08:44

Add calculated column to table ssms (sql query)

2 Answers2