Skip to Main Content
  • Questions

  • Carving up source rows into multiple target tables.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 12, 2016 - 5:26 am UTC

Last updated: August 15, 2016 - 1:23 am UTC

Version: 11.0

Viewed 1000+ times

You Asked

Sir this is my questions
************************

I have a sourcetableA with 10 records....and target tables are like(table1...table2....table5)

id
1
2
3
4
5
.
.
10...

i wnat load 2 records into table1
and next 2 records into table2
and next 2 records into table3...like how you can do it with using sql code...

2)2)See below table...

I have a table that is student.

s1 s2 s3
1000 50000 20000
2000 100000 3000000
3000 300000 40000000
1000
30000

i want max value in each collunm for first..30000....300000....400000.

and Connor said...

As long as you have some sort of sorting sequence, you can use INSERT FIRST, eg


SQL>
SQL> create table t as
  2  select owner, object_name
  3  from dba_objects
  4  where rownum < 20;

Table created.

SQL>
SQL>
SQL> create table t1 as select * from t where 1=0;

Table created.

SQL> create table t2 as select * from t where 1=0;

Table created.

SQL> create table t3 as select * from t where 1=0;

Table created.

SQL>
SQL> insert first
  2  when seq <= 2 then
  3    into t1 (owner,object_name) values (owner,object_name)
  4  when seq <= 4 then
  5    into t2 (owner,object_name) values (owner,object_name)
  6  else
  7    into t3 (owner,object_name) values (owner,object_name)
  8  select owner, object_name,
  9         row_number() over ( order by owner, object_name ) as seq
 10  from t;

19 rows created.

SQL>
SQL> select * from t1;

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            CCOL$
SYS                            CLU$

2 rows selected.

SQL> select * from t2;

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            C_COBJ#
SYS                            C_OBJ#

2 rows selected.

SQL> select * from t3;

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            C_TS#
SYS                            C_USER#
SYS                            FILE$
SYS                            ICOL$
SYS                            I_CDEF2
SYS                            I_CDEF3
SYS                            I_COBJ#
SYS                            I_COL1
SYS                            I_COL2
SYS                            I_OBJ5
SYS                            I_TS1
SYS                            I_USER2
SYS                            OBJ$
SYS                            SEG$
SYS                            TAB$

15 rows selected.

SQL>
SQL>
SQL>


Rating

  (2 ratings)

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

Comments

A reader, August 13, 2016 - 5:39 am UTC

If id don't know how many rows are there in the able then what you can do it?
Connor McDonald
August 14, 2016 - 5:44 am UTC

I'm assuming you know how many *target* tables there are, in which case you do augment the INSERT-FIRST to list all of the tables.

If you dont know how many target tables there are...well...I think you've got a larger problem than just this SQL...I think you've got a major design problem.

It's done but.

Naga, August 13, 2016 - 5:43 am UTC

If i don't know my table having a particular n.of record.For this what you can do it.
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.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.