Clever Geek Handbook

SQL create table use% type on column

I am trying to create a backup of one of my tables (in a function),

CREATE TABLE TBTestBackup
           (
             colum1           user.TBTest.colum1%type,
             colum2           user.TBTest.colum2%type,
             colum3           user.TBTest.colum3%type
             colum31           user.TBTest.colum3%type, --new column with same type as colum3
             colum4           user.TBTest2.column15type, --column from other table
             colum4           CHAR    (12 BYTE), --new column with fixed type
           ) TABLESPACE user_DATA

But I'm red, it won't work, now my question is how can I make this as dynamic as possible, so I don't have to update the datatypes when the script is backed up every time I change the datatype, eg. of:

VARCHAR2(24 CHAR)

before VARCHAR2(50 CHAR)

(table columns are fixed, they won't change) this doesn't happen often, but we had to do it several times because the field was not large enough for a certain value and then nobody updated the backup table and the id gave some errors.

EDIT: I forgot something necessary:

  • I need to add 2 columns that are not in the original table, but must be of the same datatype as one of the already existing tables. can I use select so that it has the same type but a different name? if so how to do it?
  • and some fields from another table (so I need to use joins)

SUM:

  • Multiple Columns with Multiple Table Type
  • New column with fixed type
  • New column with variable type colum XY from table ABC
+3


source to share


5 answers


Based on your updated requirements, create a table based on the types in these two tables:

create table t1 (col1 number, col2 varchar2(2), col3 date);
create table t2 (col1 varchar2(10 char));

You can just combine them together with a filter that always evaluates to false, as Orangecrush suggested:



create table tb tablespace users as
select t1.col1 as col1, t1.col2 as col2, t1.col3 as col3,
    t1.col3 as col4, t2.col1 as col5, cast(null as varchar2(12 byte)) as col6
from t1
cross join t2
where null is not null;

Usually a cross join

will be unwanted, but the optimizer is smart enough to understand that the filter means it doesn't need to click on tables at all. You can use a normal inner join if there are fields you can join, though of course.

desc tb

Name Null Type              
---- ---- ----------------- 
COL1      NUMBER            
COL2      VARCHAR2(2)       
COL3      DATE              
COL4      DATE               -- new column with same type as t1.col3
COL5      VARCHAR2(10 CHAR)  -- column from other table
COL6      VARCHAR2(12)       -- new column with fixed type
+2


source


You can create a table with:

CREATE TABLE TBTestBackup AS 
SELECT colum1, column2, column2
FROM user.TBTest


Based on your edit: you can change the select statement to whatever you like. Join two tables and select 3 columns from one table and 2 from the other.

+3


source


You can use the operator to create a backup.

CREATE TABLE TBTestBackup AS SELECT * FROM ORIGINAL_TABLE_NAME WHERE 1=2;

This assumes you do not need the data in the backup table. If you really want the data, just remove the condition WHERE

from the above statement.

+2


source


Syntax

CREATE TABLE TBTestBackup as (SELECT * FROM TBTest)

Not sure how you can automate changes to data types, but this is not something that should change often (if you have to work on it) anyway.

+2


source


Sounds like a materialized view will work exactly the way you want it to.

create materialized view my_backup
tablespace whatever
nologging
build immediate
refresh complete on demand
as
select t.* 
from some_table t;

If you want to update the backup, do a full update on the math view:

exec dbms_mview.refresh('MY_BACKUP', 'C', atomic_refresh=>false);

Also note that this does not replace RMAN or other tools used by DBAs to create backups, but is fine for what you are asking to do.

Also note that you can easily add additional columns to your query as needed.

+2


source






More articles:


All Articles