CODEWITHSUNDEEP
sqlteradata
Tedel
Tedel

Reputation: 131

How to create a new column with the table name as value - SQL

first of all I am new to using SQL. I already looked on the internet and found that I could insert a new column into a tab by using the ALTER TABLE function. Unfortunately, I don't think this is what I'm looking for since I do not want to modify the already existing table. What I'm looking for is really simple but I don't know how to do it in SQL since I mostly use R.

example :

table name = dataset1

the current data :

 ID     NAME SALARY
  1     John 100000
  2     Mike  80000
  3 Rachelle  90000

what I would like to have as my new output :

 ID     NAME SALARY FROMDATA
  1     John 100000 dataset1
  2     Mike  80000 dataset1
  3 Rachelle  90000 dataset1

I know this is ridiculously easy but I don't want to modify the data on the server I just want to make a copy of it with a column containing the dataset name that I will save after. It can even be as simple as simply creating a column with me writting manually the name of the dataset. I know it would take me 0.01 sec with R, I feel dumb but I know nothing of SQL and I have to use it today.

Thanks and sorry for bothering you guys.

Upvotes: 1

Views: 795

Answers (2)

S. Mohamad
S. Mohamad

Reputation: 87

If you are extracting data with a select you can easily add your column without actually changing the structure of your table. E.g. select *, 'dataset1' as FROMDATA from dataset1;. Another way is creating a view defined as a select of your dataset1 plus the static column you want to add

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

Select a constant field:

SELECT ID, NAME, SALARY, 'dataset1' AS FROMDATA
FROM dataset1;

This trick is more useful, and more commonly seen, when you want to take a union of two or more tables but select a column which keeps track of the table source. For example:

SELECT ID, NAME, SALARY, 'dataset1' AS FROMDATA FROM dataset1
UNION ALL
SELECT ID, NAME, SALARY, 'dataset2' FROM dataset2   -- assuming this table exists
ORDER BY FROMDATA;

Upvotes: 3

Related Questions