Home » SQL & PL/SQL » SQL & PL/SQL » INSERT using ROWNUM (oracle 9i, windowsxp)
INSERT using ROWNUM [message #325974] Mon, 09 June 2008 14:47 Go to next message
myqueries
Messages: 5
Registered: June 2008
Junior Member
Hi All,

It would be appreciate if you can respond to my query asap.

I have 2 tables. one is base table and one is staging table.
I am selecting some of the columns from base table minus some of the columns from staging table and inserting those result columns into staging table.

I want to insert only 50 rows using ROWNUM. can anybody guide me how to tune this below query with ROUNUM<=50.

inset into table_stg(
column1,
columun2,
column3,
--,
--)
select
column1,
columun2,
column3,
---,
--
from basetable
where country='US'
MINUS
select
column1,
columun2,
column3,
--,
--
from table_stg

Thanks in advance.
MK
Re: INSERT using ROWNUM [message #325977 is a reply to message #325974] Mon, 09 June 2008 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: INSERT using ROWNUM [message #325979 is a reply to message #325974] Mon, 09 June 2008 15:00 Go to previous messageGo to next message
myqueries
Messages: 5
Registered: June 2008
Junior Member
Can anybody respond..pls
Re: INSERT using ROWNUM [message #325980 is a reply to message #325979] Mon, 09 June 2008 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What is the probel with rownum <= 50?

Regards
Michel
Re: INSERT using ROWNUM [message #325981 is a reply to message #325974] Mon, 09 June 2008 15:05 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
This may help:
select * from (select rows from table1
               minus
               select rows from table2)
where rownum <= 50;

Re: INSERT using ROWNUM [message #325983 is a reply to message #325974] Mon, 09 June 2008 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What is the problem with rownum <= 50?
That is not how OP spells it.
>> how to tune this below query with ROUNUM<=50.
Re: INSERT using ROWNUM [message #325984 is a reply to message #325983] Mon, 09 June 2008 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Mon, 09 June 2008 22:06
>What is the problem with rownum <= 50?
That is not how OP spells it.
>> how to tune this below query with ROUNUM<=50.

So, I see the problem. Wink

Regards
Michel

Re: INSERT using ROWNUM [message #325988 is a reply to message #325974] Mon, 09 June 2008 15:32 Go to previous messageGo to next message
myqueries
Messages: 5
Registered: June 2008
Junior Member
Thanks for your reply Joy,

The problem is I can not do the select * from as the table has some restrictions to see all columns. I want to insert those selected columns in stg table using ROWNUM.

Please suggest me where can I use this ROWNUM in my insert statment which I mentioned.

Really appreciate your help.
Thanks,
MK
Re: INSERT using ROWNUM [message #325989 is a reply to message #325983] Mon, 09 June 2008 15:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
also, the closing bracket for the column-list is commented.
Re: INSERT using ROWNUM [message #325991 is a reply to message #325974] Mon, 09 June 2008 16:03 Go to previous messageGo to next message
myqueries
Messages: 5
Registered: June 2008
Junior Member
Hi Frank,

That is not comment, as i can't mention all colummns I mentioned like --, --, -- like this.

Thanks,
MK
Re: INSERT using ROWNUM [message #325992 is a reply to message #325974] Mon, 09 June 2008 16:11 Go to previous messageGo to next message
myqueries
Messages: 5
Registered: June 2008
Junior Member
Hi All,

I tried and got the solution by tuned my query with ROWNUM<=50 after my first select statment before MINUS.

Thanks,
MK
Re: INSERT using ROWNUM [message #326142 is a reply to message #325992] Tue, 10 June 2008 05:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've done someting like:
select
column1,
columun2,
column3,
---,
--
from basetable
where country='US'
and rownum <=50
MINUS
select
column1,
columun2,
column3,
--,
--
from table_stg

then this won't give you 50 rows as output.
It will take the first 50 rows (at random, as there's no Order by) and then remove from this set of 50 any rows match rows returned by the second half of the query.
Previous Topic: Trigger not working
Next Topic: Read text file with delimiter besides UTL & SQL* Loader
Goto Forum:
  


Current Time: Thu Dec 08 18:25:22 CST 2016

Total time taken to generate the page: 0.13210 seconds