Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query (Oracle 10g Windows)
Sql Query [message #364953] Wed, 10 December 2008 10:22 Go to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi One to All,

My Requirement is: i have a one select query which will generate some data. this data contains original data aswell as duplicates data.

Now i want put original in one table and duplicate data in to second table.iam using two temp tables.

can please provide me some instruction how can i do this.

thanks
saic
Re: Sql Query [message #364956 is a reply to message #364953] Wed, 10 December 2008 10:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe using multitable insert statement, something like:
INSERT ALL
  WHEN <row is original> THEN
    INTO <one table>
  ELSE
    INTO <second table>
SELECT <anything you want>

Search in SQL Reference documentation book for your Oracle version for the exact INSERT statement syntax.
Re: Sql Query [message #364957 is a reply to message #364956] Wed, 10 December 2008 11:02 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Yes we can do like this. but the tink is how we can divide original and duplicate data

thanks
Re: Sql Query [message #364959 is a reply to message #364957] Wed, 10 December 2008 11:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something like:
INSERT ALL
  WHEN rnum=1 THEN
    INTO <one table>
  ELSE
    INTO <second table>
SELECT row_number() over (partition by <set of columns to determine uniqueness>) rnum
      ,<all the other columns>


Further help awaitson production of a more detailed question.

[Updated on: Wed, 10 December 2008 11:10]

Report message to a moderator

Re: Sql Query [message #364963 is a reply to message #364957] Wed, 10 December 2008 12:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
saic wrote on Wed, 10 December 2008 18:02
Yes we can do like this. but the tink is how we can divide original and duplicate data

This is a good question: how would you distinguish between "original" and "duplicate" data? As your post does not contain any note about this, I have no idea what data you classify with these terms.
Re: Sql Query [message #365224 is a reply to message #364959] Thu, 11 December 2008 02:53 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
how do we assume rnum 1 is original. unless time component is incorporated
yours
dr.s.raghunathan
Re: Sql Query [message #365225 is a reply to message #364959] Thu, 11 December 2008 02:56 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
sorry for my message #365224 and sorry for the reading in between lines of Jo's reply
yours
dr.s.raghunathan
Re: Sql Query [message #365232 is a reply to message #364963] Thu, 11 December 2008 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you could argue that if the data is all duplicated, then it doesn't matter which one you use as the original - they're all the same.
Re: Sql Query [message #365239 is a reply to message #365232] Thu, 11 December 2008 03:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
JRowbottom wrote on Thu, 11 December 2008 10:09
you could argue that if the data is all duplicated, then it doesn't matter which one you use as the original - they're all the same.

It may not matter to you, but Oracle is not happy when you do not specify the ORDER BY clause. So, you shall at least issue "non-ordering" condition, as demonstrated below:
SQL> SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1)
  2  FROM (SELECT MOD(ROWNUM, 2) c1, ROWNUM c2 FROM dual CONNECT BY LEVEL <= 5);
SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1)
               *
ERROR at line 1:
ORA-30485: missing ORDER BY expression in the window specification


SQL> SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY 1)
  2  FROM (SELECT MOD(ROWNUM, 2) c1, ROWNUM c2 FROM dual CONNECT BY LEVEL <= 5);

        C1         C2 ROW_NUMBER()OVER(PARTITIONBYC1ORDERBY1)
---------- ---------- ---------------------------------------
         0          4                                       1
         0          2                                       2
         1          5                                       1
         1          1                                       2
         1          3                                       3
Re: Sql Query [message #365254 is a reply to message #365239] Thu, 11 December 2008 04:17 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Like JRowbottom said, if the records are dupes, one would not mind which to select as the "original", hence a random column can be picked for the order by
Previous Topic: Storage space allocation for user
Next Topic: Creation of view with clob column in select and group by clause (merged 3)
Goto Forum:
  


Current Time: Tue Dec 06 00:25:18 CST 2016

Total time taken to generate the page: 0.09036 seconds