Sql Help

[Sudigali_Sudeer]

We have a dynamic table that contains 1020 columns ( columns might increase for each run ) which drop and creates  on daily job.

My task is :  Need to split the columns into 2 tables equally .

Any suggestions please ?

[prabhu_pramod]

Once the daily dynamic table is ready, run the below which will give you create statements for 2 tables.

Once you get the statements, either create the table with data or just the table structure by adding where rownum < 1 .

Don't forget to add DROP tables A & B before creating new. Also add some kind of key between the tables for joining and verify that column is included in both tables.

This is in ORALCE, change accordingly for other DB's

set serveroutput on size unlimited;
declare
v_table_text1 clob :=null;
v_table_text2 clob :=null;
v_col_count number :=null;

begin
select count(1) into v_col_count from all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner';

SELECT 'CREATE TABLE A as ( SELECT ' ||LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' from '||owner||'.'||table_name ||') ;' 
into v_table_text1
FROM all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner'
and rownum <= v_col_count/2
group by owner, table_name;

SELECT 'CREATE TABLE B as ( SELECT ' ||LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' from '||owner||'.'||table_name ||') ;' 
into v_table_text2
FROM all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner'
and column_name not in (select column_name from all_tab_cols where table_name = 'TABLEA' and owner = 'TABLEA_table_owner')
group by owner, table_name;

dbms_output.put_line(v_table_text1);
dbms_output.put_line(v_table_text2);
end;
/

[Biskot]

15 hours ago, Sudigali_Sudeer said:

We have a dynamic table that contains 1020 columns ( columns might increase for each run ) which drop and creates  on daily job.

My task is :  Need to split the columns into 2 tables equally .

Any suggestions please ?

you said it may increase .. so some update work might be not committed on the background.  if you are looking for particular time to time data ......... USE NOLOCK on statements. 

[Sudigali_Sudeer]

43 minutes ago, prabhu_pramod said:

Once the daily dynamic table is ready, run the below which will give you create statements for 2 tables.

Once you get the statements, either create the table with data or just the table structure by adding where rownum < 1 .

Don't forget to add DROP tables A & B before creating new. Also add some kind of key between the tables for joining and verify that column is included in both tables.

This is in ORALCE, change accordingly for other DB's

set serveroutput on size unlimited;
declare
v_table_text1 clob :=null;
v_table_text2 clob :=null;
v_col_count number :=null;

begin
select count(1) into v_col_count from all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner';

SELECT 'CREATE TABLE A as ( SELECT ' ||LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' from '||owner||'.'||table_name ||') ;' 
into v_table_text1
FROM all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner'
and rownum <= v_col_count/2
group by owner, table_name;

SELECT 'CREATE TABLE B as ( SELECT ' ||LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' from '||owner||'.'||table_name ||') ;' 
into v_table_text2
FROM all_tab_cols
where table_name = 'dynamic_table' and owner = 'dynamic_table_owner'
and column_name not in (select column_name from all_tab_cols where table_name = 'TABLEA' and owner = 'TABLEA_table_owner')
group by owner, table_name;

dbms_output.put_line(v_table_text1);
dbms_output.put_line(v_table_text2);
end;
/

Thank you so much. I will modify it accordingly!!

[Sudigali_Sudeer]

37 minutes ago, Biskot said:

you said it may increase .. so some update work might be not committed on the background.  if you are looking for particular time to time data ......... USE NOLOCK on statements. 

Sure