0

I have a table where I would like to select certain columns and then create transformed columns based on that selection. Due to security reasons, I'm not able to create a new table and thought there may be a way to SELECT and ALTER in the same statement.

My statement below runs, but the column is not produced. Am I doing something wrong/is this approach not possible? Is there a better approach?

SELECT * col1,col2,col3 FROM db
AS db2
ALTER TABLE db2 ADD col4 AS (transformation) PERSISTED

Guidance and recommendation is appreciated.

paranormaldist
  • 489
  • 5
  • 16
  • 1
    What RDBMS are you using? How are you running the query? If it's a computed-column why not just keep it in your query as an expression or use a CTE? – Dai Oct 09 '20 at 13:46
  • Also, `SELECT TOP N` will give you meaningless results without an `ORDER BY`. – Dai Oct 09 '20 at 13:47
  • What is you real task to solve? Seems me kinda XY problem here. – Arvo Oct 09 '20 at 13:49
  • @Dai I'm new to SQL, how would I keep it as an expression? I would still need it as a column. My task is to transform some columns for use in a visualization tool, which I'm connected to – paranormaldist Oct 09 '20 at 13:50
  • Sounds to me as if you just want to create a view –  Oct 09 '20 at 13:54
  • @a_horse_with_no_name interesting, so a view has the same capabilities as create table but it isn't saved as a table? – paranormaldist Oct 09 '20 at 13:57
  • Views (they in essence are saved query definitions) are much, much more capable than tables. Tables hold data, views allow to query them in requested manner. – Arvo Oct 09 '20 at 14:16
  • @Arvo ok great! So, if the view is live connected to the table, seems like it is a much better approach – paranormaldist Oct 09 '20 at 14:53

1 Answers1

0

You likely need just perform a query or, if you want query data often, then view in database.

In SQL server and most likely in other SQL variants you can create view with next statement (db would be table name, db2 view name):

create view db2 as
select col1, col2, col3, (transformation) as col4
from db

Usually you need to grant some permissions to it, unless you are sole user of it.

Creating a view is one-time task, afterwards you can query data as follows:

select col1, col2, col3, col4
from db2
where ...
Arvo
  • 10,349
  • 1
  • 31
  • 34