Skip to Main Content
  • Questions

  • Alter table add columns provided from difference of columns from another table.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alvin.

Asked: November 15, 2016 - 7:52 am UTC

Last updated: November 18, 2016 - 4:10 am UTC

Version: Oracle 10g

Viewed 1000+ times

You Asked

Hi,

There are two tables A and B with identical columns at first.
B is a backup table of A that updated from time to time.
Table A were altered and added some columns with varchar2/date data types.

I can get the columns, data types and length of table A that table B do not have using the script below.

select column_name,data_type,data_length 
from user_tab_columns 
where table_name = 'A' 
MINUS 
select column_name,data_type,data_length 
from user_tab_columns 
where table_name = 'B'


My question is, how can I make a script that will alter table B and add columns based on the result of the script above?
E.g.
Table A
id varchar2(10),
cd varchar2(2),
cymd date,
uymd date,
cusr varchar2(20),
uusr varchar2(20),
symd date, <---- ADDED COLUMN (need to add to table B too)
rccd varchar2(2) <---- ADDED COLUMN (need to add to table B too)

Table B
id varchar2(10),
cd varchar2(2),
cymd date,
uymd date,
cusr varchar2(20),
uusr varchar2(20)


Thanks and regards,


and Connor said...

A full outer join will do the trick

SQL> create table A as select * from dba_objects where 1=0;

Table created.

SQL> create table B as select * from dba_objects where 1=0;

Table created.

SQL> alter table A drop column object_id;

Table altered.

SQL> alter table B add  new_col int;

Table altered.

SQL> alter table B modify owner varchar2(250);

Table altered.

SQL>
SQL> col a_col format a30
SQL> col b_dt format a15
SQL> col b_col format a30
SQL> col a_dt format a15
SQL>
SQL> with col_check as
  2  (
  3    select *
  4    from (
  5      select column_name a_col,data_type a_dt,data_length  a_len
  6      from user_tab_columns
  7      where table_name = 'A'
  8      ) a
  9      full outer join
 10     ( select column_name b_col,data_type b_dt,data_length  b_len
 11       from user_tab_columns
 12       where table_name = 'B'
 13     ) b
 14    on a.a_col = b.b_col
 15  )
 16  select *
 17  from   col_check;

A_COL                          A_DT                 A_LEN B_COL                          B_DT              B_LEN
------------------------------ --------------- ---------- ------------------------------ --------------- ----------
                                                          NEW_COL                        NUMBER              22
OWNER                          VARCHAR2               128 OWNER                          VARCHAR2       250
OBJECT_NAME                    VARCHAR2               128 OBJECT_NAME                    VARCHAR2       128
SUBOBJECT_NAME                 VARCHAR2               128 SUBOBJECT_NAME                 VARCHAR2       128
                                                          OBJECT_ID                      NUMBER              22
DATA_OBJECT_ID                 NUMBER                  22 DATA_OBJECT_ID                 NUMBER          22
OBJECT_TYPE                    VARCHAR2                23 OBJECT_TYPE                    VARCHAR2        23
CREATED                        DATE                     7 CREATED                        DATE             7
LAST_DDL_TIME                  DATE                     7 LAST_DDL_TIME                  DATE             7
TIMESTAMP                      VARCHAR2                19 TIMESTAMP                      VARCHAR2        19
STATUS                         VARCHAR2                 7 STATUS                         VARCHAR2         7
TEMPORARY                      VARCHAR2                 1 TEMPORARY                      VARCHAR2         1
GENERATED                      VARCHAR2                 1 GENERATED                      VARCHAR2         1
SECONDARY                      VARCHAR2                 1 SECONDARY                      VARCHAR2         1
NAMESPACE                      NUMBER                  22 NAMESPACE                      NUMBER          22
EDITION_NAME                   VARCHAR2               128 EDITION_NAME                   VARCHAR2       128
SHARING                        VARCHAR2                13 SHARING                        VARCHAR2        13
EDITIONABLE                    VARCHAR2                 1 EDITIONABLE                    VARCHAR2         1
ORACLE_MAINTAINED              VARCHAR2                 1 ORACLE_MAINTAINED              VARCHAR2         1

19 rows selected.


So from there, its easy to isolate just the rows you want

SQL> with col_check as
  2  (
  3    select *
  4    from (
  5      select column_name a_col,data_type a_dt,data_length  a_len
  6      from user_tab_columns
  7      where table_name = 'A'
  8      ) a
  9      full outer join
 10     ( select column_name b_col,data_type b_dt,data_length  b_len
 11       from user_tab_columns
 12       where table_name = 'B'
 13     ) b
 14    on a.a_col = b.b_col
 15  )
 16  select *
 17  from   col_check
 18  where  a_col is null
 19    or   b_col is null
 20    or   a_dt != b_dt
 21    or   a_len != b_len;

A_COL                          A_DT                 A_LEN B_COL                          B_DT              B_LEN
------------------------------ --------------- ---------- ------------------------------ --------------- ---------
                                                          NEW_COL                        NUMBER              22
OWNER                          VARCHAR2               128 OWNER                          VARCHAR2       250
                                                          OBJECT_ID                      NUMBER              22


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the answer, Sir.

Alvin Carrasca, November 17, 2016 - 12:58 am UTC

Thank you for your response.
However, I don't think the example you provided does help.
As I've said, using the script I provided, I can get those column name/data type/data length values.
What I want is when the result of the script is greater than 0 (it means there is/are some columns in table 'A' that do not exist in table 'B'), alter table 'B', add the result's column/s to the table 'B'.

Maybe something like this,

with diff_col as
(
 select column_name,data_type,data_length 
 from user_tab_columns 
 where table_name = 'A' 
 MINUS 
 select column_name,data_type,data_length 
 from user_tab_columns 
 where table_name = 'B'
)

alter table 'B' 
  add (
     /* loop here based from the result of diff_col? */
     columns [diff_col.column_name] [diff_col.data_type]([diff_col.data_length])
  );


I don't know if it's possible or if there's a way that's why I asked question here. I hope there is.

Again, thanks.


Connor McDonald
November 17, 2016 - 2:59 am UTC

Well - it's just a case statement isnt it ?

SQL> drop table a purge;

Table dropped.

SQL> drop table b purge;

Table dropped.

SQL>
SQL> create table A as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> create table B as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> alter table B drop column object_id;

Table altered.

SQL>
SQL> alter table B add  new_col int;

Table altered.

SQL>
SQL> alter table B modify owner varchar2(250);

Table altered.

SQL>
SQL> with col_check as
  2  (
  3    select *
  4    from (
  5      select column_name a_col,data_type a_dt,data_length  a_len
  6      from user_tab_columns
  7      where table_name = 'A'
  8      ) a
  9      full outer join
 10     ( select column_name b_col,data_type b_dt,data_length  b_len
 11       from user_tab_columns
 12       where table_name = 'B'
 13     ) b
 14    on a.a_col = b.b_col
 15  )
 16  select a_col, b_col,
 17    case
 18      when a_col is null then 'alter table a add '||b_col||' '||b_dt||'('||b_len||')'';'
 19      when b_col is null then 'alter table b add '||a_col||' '||a_dt||'('||a_len||')'';'
 20      else 'alter table b modify '||b_col||' '||b_dt||'('||a_len||')'';'
 21    end ddl
 22  from   col_check
 23  where  a_col is null
 24  or   b_col is null
 25  or   a_dt != b_dt
 26  or   a_len != b_len;

A_COL                          B_COL                          DDL
------------------------------ ------------------------------ --------------------------------------------------
OWNER                          OWNER                          alter table b modify OWNER VARCHAR2(128)';
                               NEW_COL                        alter table a add NEW_COL NUMBER(22)';
OBJECT_ID                                                     alter table b add OBJECT_ID NUMBER(22)';

3 rows selected.

SQL>


You would need to decide who is the "winner" (ie, which table is "correct" when differences occur)

Thank you again for the quick response

Alvin Carrasca, November 17, 2016 - 7:16 am UTC

Thank you for the followup answer.

Your latest answer helped me to figure out about generating ddl script, it didn't occur to my mind. I was thinking that there might be a way in just one run of script. ^^ I guess that's not possible.
I think that would be the best solution, to generate ddl script.

Thanks a lot. ;)
Connor McDonald
November 18, 2016 - 4:10 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.