CODEWITHSUNDEEP
sqloracle-database
AloneInTheDark
AloneInTheDark

Reputation: 938

Inserting columns to another table

I had a sql query which brings me info about tablespaces and it's sizes. I'm using this query for an oracle database.

QUERY

SELECT
  df.tablespace_name                                                  tablespace,
  (df.total_space_mb - fs.free_space_mb)                              kullanilan_alan,
  fs.free_space_mb                                                    bos_alan,
  ROUND(100 * ((df.total_space - fs.free_space) / df.total_space), 2) doluluk_orani
  FROM (
       SELECT
         tablespace_name,
         SUM(bytes)                  total_space,
         ROUND(SUM(bytes) / 1048576) total_space_mb
       FROM dba_data_files
       GROUP BY tablespace_name) df,
  (
    SELECT
      tablespace_name,
      SUM(bytes)                  free_space,
      ROUND(SUM(bytes) / 1048576) free_space_mb
    FROM dba_free_space
    GROUP BY tablespace_name) fs
 WHERE df.tablespace_name = fs.tablespace_name (+)
 ORDER BY fs.tablespace_name;

This gives me a result like this:

system - 322 - 23 - 92.32

The query above shows me 4 columns. I created a new table which has 5 columns, which has a date field. So i need to copy the result of the query above, plus the date. Here is example:

system - 322 - 23 - 92.32 - 01/09/2014

I'm not good in sql, so i tried too much but failed.

Upvotes: 0

Views: 69

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can get todays date by SYSDATE and append to select list for inserting in new table.

INSERT INTO newTable(colA,colB,colC,colD,DateColumn)

SELECT df.tablespace_name TABLESPACE,               
  (df.total_space_mb - fs.free_space_mb) KULLANILAN_ALAN,              
  fs.free_space_mb BOS_ALAN,              
  ROUND(100 * ((df.total_space - fs.free_space) / df.total_space),2) DOLULUK_ORANI ,

  SYSDATE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,              
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB              
      FROM dba_data_files              
      GROUP BY tablespace_name) df,              
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,              
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB              
       FROM dba_free_space              
       GROUP BY tablespace_name) fs              
WHERE df.tablespace_name = fs.tablespace_name(+)              
ORDER BY fs.tablespace_name;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You get the current day with:

select ..., trunc(sysdate) from ...

So far as to answering your question. I would additionally like to recommend two things: 1. Use the new join syntax (which is left outer join instead of the (+) operator), 2. divide as late as possible to avoid rounding errors. Having rounded 2.6 to 3 and 1.4 to 1 gives you 3-1=2 instead of round(2.6-1.4)=1 for instance.

select
  tablespace_name as tablespace,
  round((df.total_space_mb - fs.free_space_mb) / 1048576) as kullanilan_alan,
  round(fs.free_space / 1048576) as bos_alan,
  round((df.total_space - fs.free_space) / df.total_space * 100, 2) as doluluk_orani
  -- , trunc(sysdate) as current_day
from
(
  select tablespace_name, sum(bytes) as total_space
  from dba_data_files
  group by tablespace_name
) df
left outer join
(
  select tablespace_name, sum(bytes) as free_space
  from dba_free_space
  group by tablespace_name
) fs
using (tablespace_name = tablespace_name)
order by tablespace_name;

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

you can always use a "constant" value in a select (and so in an insert... select)

so this is perfectly valid :

insert into table2 (id, f1, f2, f3, f4, fdate, farbitrary)
select id, field1, field2, field3, field4, SYSDATE, 'I put what I want here')
from table1

In this case, id, field1, field2, field3, field4 are fields of table1, while SYSDATE is... the current date and time (and 'I put what I want here' is a "constant" string)

Upvotes: 1

Related Questions